What Rows are sent to DM?

saurabh39 · Posted: Sat Mar 27, 2010 12:33 am

Hi All,

The following is the process of sql execution -
SQL query is given to RDs, which optimises the query and gives it to DM. Then DM will send it to BM. And B<M in turn will give the pages from Buffer or raise a request for read from table. and then the data goes to DM. WHich applies Stage 1 predicates on it.

The question is, when stage 1 predicate is applied after sending the pages to DM, that means when a call is made to BM, the where clause has not been evaluated so what pages are to be sent to DM, how that is determined, How BM will know what pages to be sent.

sushanth bobby · Posted: Sat Mar 27, 2010 1:53 am

Tushar,

I think filtering is applied at both times during requesting & sending

Requesting Part
RDS(optimises the sql) --> DM(tries to decrease the amount of data to be accessed) --> BM(get the data)

Sending Part
BM(gets the data) --> DM(tries to decrease the amount of data received from BM by applying STAGE 1) --> RDS(Stage 2 predicates applied)

Sushanth

saurabh39 · Posted: Sat Mar 27, 2010 4:02 am

Hi Sushant,

sushanth bobby · Posted: Sat Mar 27, 2010 6:52 pm

Tushar,

DM tries to narrow down its search using indexes & statistics.

Tell me how there can be a search without using WHERE clause, the search without using where clause will be a tablespace scan. Indexes are created to avoid tablespace scan. So, WHERE clause will be considered by DM definately.

Sushanth

saurabh39 · Posted: Sat Mar 27, 2010 11:08 pm

Hi Sushant,

If DM applies the stats and index before sending it to BM, does it mean STAGE 1 predicate is applied at DM before sending it to BM?

sushanth bobby · Posted: Sun Mar 28, 2010 4:02 am

Saurabh,

DM uses the available statistics, indexes and sends that info to BM to retreive rows. And later after BM gets the rows, sends it to DM, this is where Stage 1 is applied.

Sushanth