Home » RDBMS Server » Performance Tuning » Doubt about Tuning SQL (Oracle 11g R2)
| Doubt about Tuning SQL [message #529935] |
Thu, 03 November 2011 11:32  |
 |
aucrun
Messages: 114
Registered: February 2011
|
Senior Member |
|
|
Hi,
First of all I would like to explain that this is not an problem, but, an question/doubt instead.
I'll keep it the simpliest I can manage.
Imagine I have the following table with several thousand records. (in the sample I only have few)
Table Creation:
CREATE TABLE "AAA_PERSON"
( "COD_PERSON" NUMBER,
"NAME" VARCHAR2(50 BYTE),
"ZIPCODE" NUMBER,
"PHONE" NUMBER,
"NICKNAME" VARCHAR2(20 BYTE),
"DATA_NASC" TIMESTAMP (3)
) ;
CREATE INDEX "AAA_NOME_PERSON" ON "AAA_PERSON" ("NAME");
Sample Data:
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (6, 'Anne', '1222', 983242442, 'Anne', '1975-02-03');
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (7, 'Diane', '4433', 32424232, 'Diane', '1988-08-23');
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (8, 'Jonh', '4244', 43534353, 'Jonh', '1980-11-14');
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (9, 'Paul', '3312', 131313131, 'Paul', '1971-12-11');
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (10, 'Peter', '1222', 433453535, 'Peter', '1965-08-24');
insert into aaa_person (cod_person, name, zipcode, phone, nickname, data_nasc)
values (11, 'Milene', '5555', 534535, 'Milene', '1973-05-15');
In a more complex scenario I could have an extreme complicated query relating this table with some others, but in my simple scenario I only have this.
Imagine I want to run as simple statement just like this:
SELECT name, zipcode, phone FROM AAA_PERSON WHERE name is not null ORDER BY name;
One feature over Oracle (from Oracle 9 I guess) is the possibility to refer to table Index directly in a way to tune the statement performance, just like the sample below):
SELECT /*+ ORDERED INDEX (aaa_person 'AAA_NOME_PERSON') */
name, zipcode, phone FROM AAA_PERSON WHERE name is not null;
(this statement only returns the data without sort, but optimizing by using the Index I've mentioned)
Bu my question is, is there any way to use the Index to Sort the statement results, instead of using the SORT BY clause?
Thanks!
[Updated on: Thu, 03 November 2011 11:33] Report message to a moderator |
|
|
|
|
|
| Re: Doubt about Tuning SQL [message #529940 is a reply to message #529935] |
Thu, 03 November 2011 11:44  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Running your test, the index is used by the first query, no hints needed:orcl> set autot on exp
orcl> SELECT name, zipcode, phone FROM AAA_PERSON WHERE name is not null ORDER BY name;
NAME ZIPCODE PHONE
-------------------------------------------------- ---------- ----------
Anne 1222 983242442
Diane 4433 32424232
Jonh 4244 43534353
Milene 5555 534535
Paul 3312 131313131
Peter 1222 433453535
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956904908
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 318 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| AAA_PERSON | 6 | 318 | 0 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | AAA_NOME_PERSON | 6 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
orcl>
What more would you want?
|
|
|
|
|
|
| Re: Doubt about Tuning SQL [message #529943 is a reply to message #529941] |
Thu, 03 November 2011 11:46 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your second query, with the attempt at a hint, doesn't use the index:orcl> SELECT /*+ ORDERED INDEX (aaa_person 'AAA_NOME_PERSON') */
2 name, zipcode, phone FROM AAA_PERSON WHERE name is not null;
NAME ZIPCODE PHONE
-------------------------------------------------- ---------- ----------
Anne 1222 983242442
Diane 4433 32424232
Jonh 4244 43534353
Paul 3312 131313131
Peter 1222 433453535
Milene 5555 534535
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1340481993
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 318 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL| AAA_PERSON | 6 | 318 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage("NAME" IS NOT NULL)
filter("NAME" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
orcl> |
|
|
|
|
|
| Re: Doubt about Tuning SQL [message #529950 is a reply to message #529948] |
Thu, 03 November 2011 12:07 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
What we have proved is that the optimizer may or may not choose to use the index, and that your hint is being ignored.
It is in any case an impossible hint: ORDERED is meaningless when you are querying only one table.
(update: I've just seen the syntax error: you have the uindex name in single quotes. That invalidates the hint. )
[Updated on: Thu, 03 November 2011 12:11] Report message to a moderator |
|
|
|
| Re: Doubt about Tuning SQL [message #529951 is a reply to message #529948] |
Thu, 03 November 2011 12:13 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
aucrun wrote on Thu, 03 November 2011 17:00Yes it does!
Oh No it doesn't.
Ordered doesn't do what you think it does. Maybe you should read up on what it does in the docs.
The fact that you got the data back in name order is a complete coincidence.
EDIT: typo
[Updated on: Thu, 03 November 2011 12:14] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 13:06:51 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
