| outline not working with order by clause [message #191421] |
Wed, 06 September 2006 05:32  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Hi,
I have table test_otln with column t1. I want to execute statement select * from test_otln. when this statements gets executed it should give me sorted data. this i am trying to achieve by creating ouline.
I have done the following steps but outline is getting used but its not sorting data.
Can anybody tell me how i can create outline for statement select * from test_otln order by t1 so that when we issue statement select * from test_ptln outline for above statement is used and i get sorted data.
I hope i am clear what i want to say.
app> SELECT * FROM TEST_OTLN;
T1
----------
1
2
10
1000
500
100000
6 rows selected.
app> GRANT SELECT ON TEST_OTLN TO PLAY;
Grant succeeded.
app> @CONNECT PLAY/PLAY@PINIDC
Connected.
GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
play
play> CREATE VIEW TEST_OTLN AS SELECT * FROM APP.TEST_OTLN;
View created.
play> SELECT * FROM TEST_OTLN;
T1
----------
1
2
10
1000
500
100000
6 rows selected.
play> CREATE OUTLINE TEST_OT ON SELECT * FROM TEST_OTLN;
Outline created.
play> SELECT * FROM USER_OUTLINES;
NAME CATEGORY USED TIMESTAMP VERSION SQL_TEXT SIGNATURE
------------------------------ ------------------------------ --------- --------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
TEST_OT DEFAULT UNUSED 06-SEP-06 9.2.0.4.0 SELECT * FROM TEST_OTLN EC2D4452DF0A7F378FD03E91B62508BC
play> @CONNECT APP/ONE
Connected.
GLOBAL_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
app
app> SELECT * FROM TEST_OTLN;
T1
----------
1
2
10
1000
500
100000
6 rows selected.
app> SELECT * FROM DBA_OUTLINES;
NAME OWNER CATEGORY USED TIMESTAMP VERSION SQL_TEXT SIGNATURE
------------------------------ ------------------------------ ------------------------------ --------- --------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
TEST_OT PLAY DEFAULT UNUSED 06-SEP-06 9.2.0.4.0 SELECT * FROM TEST_OTLN EC2D4452DF0A7F378FD03E91B62508BC
app> ALTER SESSION SET USE_STORED_OUTLINES = TRUE;
Session altered.
app> SELECT * FROM TEST_OTLN;
T1
----------
1
2
10
1000
500
100000
6 rows selected.
app> SELECT * FROM DBA_OUTLINES;
NAME OWNER CATEGORY USED TIMESTAMP VERSION SQL_TEXT SIGNATURE
------------------------------ ------------------------------ ------------------------------ --------- --------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------
TEST_OT PLAY DEFAULT USED 06-SEP-06 9.2.0.4.0 SELECT * FROM TEST_OTLN EC2D4452DF0A7F378FD03E91B62508BC
app>
Thanks & Regards
Chandan Singh
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
