Index

[toolsmani]

Am having a primary key on a column called "x" and not null column on "Y" which will have repeating values .

select * from table_name where y='...' ;

it will take full table scan.But i need to reduce the cost and time to retrieve the row values.whatz the solution?

[Dave44]

Am having a primary key on a column called "x" and not null column on "Y" which will have repeating values .

select * from table_name where y='...' ;

it will take full table scan.But i need to reduce the cost and time to retrieve the row values.whatz the solution?

what kind of values will Y have? strings or numbers?

are there any other fields in the table that can be used to restrict the data?

[vijaydiwakar]

Am having a primary key on a column called "x" and not null column on "Y" which will have repeating values .

select * from table_name where y='...' ;

it will take full table scan.But i need to reduce the cost and time to retrieve the row values.whatz the solution?

pls explain with some data and tbl desc

[toolsmani]

pls explain with some data and tbl desc

x and y are numbers and x is the primary key and we can allow repeating values for y and search criteria is on y .

[vijaydiwakar]

x and y are numbers and x is the primary key and we can allow repeating values for y and search criteria is on y .

See the basic aim to have PK is to reduce the time and redudant calls as ur search is on Y which is not PK then Ora will cunsume resources and hence increses the time of execution
though u can use Index on column Y
try it
Good Luck

[Dave44]

x and y are numbers and x is the primary key and we can allow repeating values for y and search criteria is on y .

If there is no index (thus logical way for oracle) to reduce the rows it needs to look at then a full table scan will have to be done so oracle can be sure it doesnt miss any.

Also even with an index on the field, if oracle determines that more than 20% of the rows need to read it becomes faster for oracle to perform a full scan anyway.

How large would this table be (or get to be)?