Monday, June 02, 2008

Seems like a good idea...

Check it out - and comment away...  Tyler has a good idea/thought going on over there.

Also (total context switch here) this is the coolest new bit of SQL I've had the pleasure of writing for a while (11g and above only...)

 

ops$tkyte%ORA11GR1> with data
2 as
3 ( select when, thing, val
4 from t2
5 unpivot ( val for thing in
6 ( LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN,
7 BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
8 DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS,
9 MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE,
10 PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS,
11 PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE )
12 )
13 )
14 select THING, before, middle, coalesce, rebuild, rebuild-coalesce diff
15 from data
16 pivot( max(val) for when in ( 'before' as before, 'middle' as middle,
17 'coales' as coalesce, 'rebuil' as rebuild )
18 )
19 order by thing
20 /

THING BEFORE MIDDLE COALESCE REBUILD DIFF
-------------------- ---------- ---------- ---------- ---------- ----------
BLKS_GETS_PER_ACCESS 4 4 4 4 0
BR_BLKS 6 6 6 4 -2
BR_BLK_LEN 8028 8028 8028 8028 0
BR_ROWS 2810 2810 1409 1405 -4
BR_ROWS_LEN 33639 33639 16856 16809 -47
BTREE_SPACE 22524924 22524924 11322528 11274488 -48040
DEL_LF_ROWS 0 238415 0 0 0
DEL_LF_ROWS_LEN 0 9601917 0 0 0
DISTINCT_KEYS 499999 488415 250000 250000 0
LF_BLKS 2811 2811 1410 1406 -4
LF_BLK_LEN 7996 7996 7996 7996 0
LF_ROWS 499999 488415 250000 250000 0
LF_ROWS_LEN 20121735 19665121 10063204 10063204 0
MOST_REPEATED_KEY 1 1 1 1 0
OPT_CMPR_COUNT 0 0 0 0 0
OPT_CMPR_PCTSAVE 0 0 0 0 0
PCT_USED 90 88 90 90 0
PRE_ROWS 0 0 0 0 0
PRE_ROWS_LEN 0 0 0 0 0
ROWS_PER_KEY 1 1 1 1 0
USED_SPACE 20155374 19698760 10080060 10080013 -47

21 rows selected.


I used pivot to turn my rows into columns and then unpivot to turn some of my columns into rows (I flipped this result side on its side!)



A bit of background, I wanted to compare the effects of:




  • freshly built index (before)


  • to an index that had lots of deletes done to it (middle)


  • to an index that was just coalesced after those deletes (coalesce)


  • to a rebuilt index (rebuild)



So, in order to capture the information I just did this:



ops$tkyte%ORA11GR1> create index t_idx on t(x,object_name);
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;

ops$tkyte%ORA11GR1> create table t2
2 as
3 select 'before' when, index_stats.* from index_stats;

delete data here....

ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'middle' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx coalesce;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'coales' when, index_stats.* from index_stats;

ops$tkyte%ORA11GR1> alter index t_idx rebuild;
ops$tkyte%ORA11GR1> analyze index t_idx validate structure;
ops$tkyte%ORA11GR1> insert into t2
2 select 'rebuil' when, index_stats.* from index_stats;


Now, index_stats has lots of columns... and my table T2 has very few rows - so, if I turn my rows into columns and columns into rows, I'd be able to better visualize this particular set of data on screen (in sqlplus of course...)



That is what that pivot/unpivot query did for me - I like it...



 



Looks better than this did :)



 



ops$tkyte%ORA11GR1> select * from t2;

WHEN HEIGHT BLOCKS NAME
------ ---------- ---------- ------------------------------
PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------------- ---------- ------------ -------------- ----------------
before 3 2944 T_IDX
499999 2811 20121735 7996
2810 6 33639 8028 0 0
499999 1 22524924 20155374 90 1
4 0 0 0 0

middle 3 2944 T_IDX
488415 2811 19665121 7996
2810 6 33639 8028 238415 9601917
488415 1 22524924 19698760 88 1
4 0 0 0 0

coales 3 2944 T_IDX
250000 1410 10063204 7996
1409 6 16856 8028 0 0
250000 1 11322528 10080060 90 1
4 0 0 0 0

rebuil 3 1536 T_IDX
250000 1406 10063204 7996
1405 4 16809 8028 0 0
250000 1 11274488 10080013 90 1
4 0 0 0 0
POST A COMMENT

10 Comments:

Anonymous Kevin T said....

Tom:

In regards to Tyler's comments, Steven Feuerstein used to have a couple of discussion groups back in the day as part of the RevealNet group, prior to it's sale to Quest Corp. They would often distribute code tips and such, and would compile them into a library he collected called 'PL/Vision'.

Surprisingly, Quest still distributes the library via their 'Toad' section, at the location:

http://www.toadworld.com/Knowledge/DatabaseKnowledge/StevenFeuersteinsPLSQLObsession/MyPetProjectsandContributions/PLVisionCodeLibrary/tabid/316/Default.aspx

It's quite a collection of utility PL/SQL code, some of which is dated, but it had the right idea, in collecting great generic utilities into a useful library.

Unfortunately, time has marched on, and no one has taken back up the charge of updating/revitalizing the concept. Perhaps the new Oracle Wiki site will help keep things current.

Cheers!

Mon Jun 02, 05:52:00 PM EDT  

Blogger Karen said....

I'm giddy over the pivot/unpivot functionality in 11. Now all I have to do is get the opportunity to use 11 more so I can take advantage of it. Trying to pivot/unpivot pre-11 can be a royal pain in the backside.

Really nice example.

Mon Jun 02, 09:31:00 PM EDT  

Blogger Kim Berg Hansen said....

Great pivot/unpivot example, Tom, thanks.

Did you see comment # 2 to Tyler's blog? Made me think of your discussions on TAPI's version XAPI's :-)

And I think that might be a reason for not so many PL/SQL "libraries" out there - when developers most often use PL/SQL for data-oriented code they don't think as often of using PL/SQL for more "generic" code. It can be easier to load a java library into the database, sometimes :-)

But yes, it would be lovely if the community could agree on a common well-known repository for sharing "libraries" - might even have a few bits hanging around that could be polished and made suitable for contribution :-)

Tue Jun 03, 07:48:00 AM EDT  

Blogger Tanel Poder said....

Very cool example indeed!

Btw, when I have an issue with too wide line size in query output, I just run a script which pops a web browser window up with the query results.

The script is very simple:

------------------------------
-- html.sql - sqlplus htmlizer
------------------------------
set termout off markup html on spool on
spool output_&_connect_identifier..html

l
/
spool off

set termout on markup html off spool off
host start output_&_connect_identifier..html

------------------------------

Wed Jun 04, 09:40:00 AM EDT  

Anonymous Anonymous said....

Tom,

OK waaaaaay off topic but I was wondering if you're writing another book? I thought you were but I haven't read heard about it for a while.

Hope you're not fed up with people clamouring about the book's progress!

Gareth

Wed Jun 04, 12:50:00 PM EDT  

Anonymous Anonymous said....

This has nothing to do with your blog post. It has to do with AskTom and I can't find any place to comment on the website. After reading a post it always returns to the first page of 15 posts, no matter what post I am on. Very annoying. Also it would be nice to sort by the column headers.

Thu Jun 05, 12:58:00 PM EDT  

Blogger Thomas Kyte said....

@Anonymous -

click the checkbox on the bottom right hand side to open questions in new window ....

That'll fix the "returns to first page" issue and should be "sticky" (you don't have to set it every time)

as for column headings - never noticed that, the only time I want to sort is when I've searched and when you search - they are clickable.

I think that is ok though - You don't need to sort by subject or last asked when just browsing the entire set of questions...

Thu Jun 05, 01:11:00 PM EDT  

Blogger Tom said....

I agree with Anonymous about AskTom. It would be nice to have a choice when browsing to order by "First Asked" instead of "Last Updated" when browsing, especially now that you are taking new questions on a somewhat regular basis.

Fri Jun 06, 12:35:00 PM EDT  

Blogger Johan said....

This is also the biggest issue I have with SQL Plus.

Why can't it just display this kind of output in a more sensible way out of the box?

Sun Jun 08, 06:46:00 PM EDT  

Anonymous Jan said....

I was all excited about the pivot, then I noticed the 'only 11g and above'. I'll just have to remember that one for later, and do it the hard way for now.. which you've explained very well.


About Tyler comments, I don't get it why PL/SQL isn't suited for libraries. We use several packages in our Apex projects. When you split you problems in smaller bits, so does your code results in more specific procedures and you're getting reusable components for free.
(Although I do notice I'm one of the few contributing to these packages, Maybe because I'm a rookie that started of programming Java?)

Anyway, thanks Tom for a great blog.

Fri Jul 18, 05:28:00 PM EDT  

POST A COMMENT

<< Home