Home » SQL & PL/SQL » SQL & PL/SQL » Explain SQL and using an Index
Explain SQL and using an Index [message #189236] Wed, 23 August 2006 15:09 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I'm trying to understand why certain returned columns in an SQL statement use an index and some don't.

CREATE TABLE SERVER
(
  SERVER_NAME        VARCHAR2(40 BYTE),
  SEQUENCE           NUMBER,
  EFFSEQ             NUMBER,
  STATUS             VARCHAR2(1 BYTE),
  LOCATION_KEY       VARCHAR2(10 BYTE),
  BEGIN_RACK_NUMBER  NUMBER,
  END_RACK_NUMBER    NUMBER,
  PUBLIC_IP_ADDRESS  VARCHAR2(15 BYTE),
  COMMENTS           VARCHAR2(4000 BYTE),
  CREATE_SSO_ID      VARCHAR2(20 BYTE),
  EFFDT              DATE
)

CREATE UNIQUE INDEX PK_SERVER ON SERVER
(SERVER_NAME, SEQUENCE, EFFSEQ, STATUS)

ALTER TABLE SERVER ADD (
  CONSTRAINT PK_SERVER
 PRIMARY KEY
 (SERVER_NAME, SEQUENCE, EFFSEQ, STATUS)
    USING INDEX)


This uses an index.

select server_name,
	         sequence
      from (select server_name,
	               sequence,
                   effseq,
				   status,
				   location_key,
				   begin_rack_number,
				   end_rack_number,
				   public_ip_address,
				   max(effseq) over (partition by sequence) max_effseq
			  from server) a
	    where a.status       = 'A' and
		      a.effseq       = a.max_effseq
        order by 1

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		134  	 	3  	 	      	             	 
  SORT ORDER BY		134  	6 K	3  	 	      	             	 
    VIEW		134  	6 K	2  	 	      	             	 
      WINDOW SORT		134  	2 K	2  	 	      	             	 
        INDEX FULL SCAN	IS_SERVERS.PK_SERVER	134  	2 K	1  	 	      	             	 


This doesn't use an index

select server_name,
	         sequence,
			 location_key
      from (select server_name,
	               sequence,
                   effseq,
				   status,
				   location_key,
				   begin_rack_number,
				   end_rack_number,
				   public_ip_address,
				   max(effseq) over (partition by sequence) max_effseq
			  from server) a
	    where a.status       = 'A' and
		      a.effseq       = a.max_effseq
        order by 1


Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		134  	 	5  	 	      	             	 
  SORT ORDER BY		134  	7 K	5  	 	      	             	 
    VIEW		134  	7 K	4  	 	      	             	 
      WINDOW SORT		134  	3 K	4  	 	      	             	 
        TABLE ACCESS FULL	IS_SERVERS.SERVER	134  	3 K	3  	 	      	             	 


Is the SQL statement not written correctly?
Re: Explain SQL and using an Index [message #189238 is a reply to message #189236] Wed, 23 August 2006 15:20 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The first query can provide the result using only the columns in the index, thus the index full scan. Even though you are referring to non-indexed columns in the inline view, the ultimate outside select does not reference any of those columns so Oracle optimizes those references out.

The second query lists location_key as a column in the final select and since this is not present in the index, the optimizer (correctly) determines that a FTS is better than a full index scan/table rowid lookup combination.

Could you move that status = 'A' filter into the inline view?
Re: Explain SQL and using an Index [message #189241 is a reply to message #189238] Wed, 23 August 2006 15:43 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Todd Barry wrote on Wed, 23 August 2006 15:20

Could you move that status = 'A' filter into the inline view?


I don't believe so. It has to do with the effseq of the record, meaning, I only want the greatest effseq of that record and it must be 'A - Active'. It's possible to have a greater effseq for the record but be 'I - Inactive'. Follow me on that?

If I moved the status filter into the inline view than I might not be getting the latest effseq for that record.

Ok, back to me wanting to understand how all of this is working. What do people do when they need 3,4,5 or more columns from a table? I mean, I could ask for the 5th column, 10th column or any combination of columns. How do I write efficient SQL that encompasses all those combinations? It just seems to me that an index will only be used if you are justing asking for a primary key column and nothing else. I don't understand that.
Re: Explain SQL and using an Index [message #189244 is a reply to message #189241] Wed, 23 August 2006 16:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Totally understand about the status filter. I knew the positioning might be significant, but I had to ask because some people leave the filtering to later stages and miss out on some optimization possibilities.

The index is generally going to be useful to quickly identify the subset of rows in which you are interested. In your query, the optimizer HAS to do a full scan of something because it has to look at every row, or at least part of every row, so it's either a FTS or a FIS.

If you have a skinny index (based on a small percentage of the total number of column in the table), then a FIS can be more efficient than a FTS. In your case, there's probably not much difference.

Don't write your SQL based on the index. Write the SQL you need to get the desired result and then see what the CBO is doing to get you there - you may need a new index, but maybe not.

Don't get too hung up on whether or not the CBO is using an index - FTS are not always bad and often are more efficient than an index lookup, and often comparable to a FIS.
Re: Explain SQL and using an Index [message #189246 is a reply to message #189244] Wed, 23 August 2006 16:22 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Todd Barry wrote on Wed, 23 August 2006 16:04

I knew the positioning might be significant, but I had to ask because some people leave the filtering to later stages and miss out on some optimization possibilities.


That's good to know. I've actually written SQL both ways and I even wondered if one was better than the other, especially when the explain plan didn't show any difference.

I must be trying too hard. I try to make every application as fast as possible so I look at every detail. It just seems that every table would need 50+ indexes if you looked at every single combination of column you might need. That's what I didn't quite understand.

What would you do, say, if you wanted location_key or maybe begin_rack_number, end_rack_number, public_ip_address or any combination of those columns?
Re: Explain SQL and using an Index [message #189248 is a reply to message #189246] Wed, 23 August 2006 16:41 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Again, in your query, I'm not sure you even need an index. That inline view has to look at every row (or subset of a row) anyway. I would start with no index at all and see how it goes.
Re: Explain SQL and using an Index [message #189252 is a reply to message #189236] Wed, 23 August 2006 16:54 Go to previous message
Duane
Messages: 452
Registered: December 2002
Senior Member
One last question. Could the table be designed differently for better results? I just do another record insert instead of updating the record.

server_name, sequence, effseq, status, public_ip_address, ....

Server1, 1, 0, A, ADM, 111.111.1.1, ....
Server1, 1, 1, A, ADM, 111.123.2.3, ....

Server2, 2, 0, A, ADM, 111.111.1.1, ....
Server2, 2, 1, I, ADM, 111.111.1.1, ....

Server3, 3, 0, A, ADM, 111.121.1.1, ....
Server3, 3, 1, A, ADM, 111.111.1.3, ....
Server3, 3, 2, A, ADM, 111.145.1.4, ....
Server3, 3, 3, A, ADM, 111.111.1.1, ....

[Updated on: Wed, 23 August 2006 16:55]

Report message to a moderator

Previous Topic: Data Pump
Next Topic: CONNECT BY - Having root node displayed in every row
Goto Forum:
  


Current Time: Sun Dec 04 10:47:54 CST 2016

Total time taken to generate the page: 0.07560 seconds