select emp_num from emp SAMPLE(20);

[AJCG]

All,

I wonder if you can be some help, the above query returns a random 20% of the table emp.. I would like to beef this up to return a random 20000 rows (845000 in the table) from another table (clients) after I have already done a set of filters in the sql statement i.e.

select *
from le_clients a, le_flags b
where lc_client_id = lf_client_id
and LF_DECEASED_FLAG is null
and LF_MPS_FLAG is null
and LF_GONEAWAY is null
and lc_cust_flag is null
and LF_ET_FLAG is null
and LF_BEREAVE is null
and LF_SCR_OUT is null
and LF_CHIEF_EXEC is NULL
and LF_F2FSUPP_FLAG is null
and lf_utility_flag is null
and LF_STATUS = 'F2F'
and lf_catchment = 'IN_AREA'

Any help would be great....

Rgards

Adam

 

[jimbopalmer]

Oracle does not index by null, so all the filters by null will not speed up the query. so the columns to make sure are indexed are


where lc_client_id = lf_client_id
and LF_STATUS = 'F2F'
and lf_catchment = 'IN_AREA'

these 4 columns could be indexed

lf_client_id, LF_STATUS, lf_catchment might make a concatenated index

create index jimbo_index on le_flags (lf_client_id, LF_STATUS, lf_catchment) tablespace index_space

where index_space is whereever you keep your indexes



(Flags that use T or F + or - or somesuch are much more indexable (bitmap indexes) that flags where one value is null) I tried to remain child-like, all I acheived was childish.

 

[AJCG]

Ermm I think I may have communicated the wrong issue? I was asking about selecting a random set of rows from 800000 returned from the original query...?

The query on the other hand is ruynning very fast anyway? But thanks for the suggestions!

Regards

Adam