Re: Set explain what does it mean?

In article <514dep$lvf@cssun.mathcs.emory.edu>, "\\"htale\\"" <htale@hcia.com> writes > I have two tables the first, test_tbl1, has an index on column of > varchar datatype. The second table, test_tbl2 has index on column of > char datatype. Running SET EXPLAIN ON produced the following. > > -What is the different between the two queries? No much - see below. > -What the effect of having index on column of varchar datatype? > Never tried using varchar's so I'm not sure. > > Thanks in advance, > > Haytham > > test_tbl2 is defined with data type char > > QUERY: > ------ > select count(*) from test_tbl2 > where client_id = '001' and market_id = '001' > and lname = 'ADAMS'> > That's the query > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Estimated cost in terms of CPU and disk/network resources only meaningful when compared with different query executions on the same server. > > 1) test_tbl2: INDEX PATH Index will be used to access this table. > > (1) Index Keys: client_id market_id lname (Key-Only) The index on client_id market_id lname will be used. Key Only search means no need to read the data rows, the results of the query can be found by only looking at the index. This is the fatest form of indexed search. > Lower Index Filter: (test_prospect.client_id = '001' AND > (test_prospect.market_id = '001' AND test_prospect.lname = 'ADAMS' ) ) > Heres is the filter used (part of where clause satisfied by the index). > > > test_tbl2 is defined with data type varchar > QUERY: > ------ > select count(*) from test_tbl1 > where client_id = '001' and market_id = '001' > and lname = 'ADAMS'> > > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > > 1) test_prospect: INDEX PATH > > (1) Index Keys: client_id market_id lname > Lower Index Filter: (test_prospect.client_id = '001' AND > (test_prospect.market_id = '001' AND test_prospect.lname = 'ADAMS' ) ) > > Same here except not a keyonly search - the actual rows of data have to be read satisfy the query, this implys varchars are indexed differently than (isn't the key value stored in the index for varchars?? -- David Williams