IDS Forum

Re: SKIP SCAN

Posted By: Fernando Nunes
Date: Tuesday, 2 August 2016, at 8:34 a.m.

In Response To: Re: SKIP SCAN (Fernando Nunes)

My mistake....:

SELECT

count(*)
FROM

dataset_info
WHERE

item_id=114555365 AND (path_status = 'NEWPATH' OR EXISTS (SELECT 1 FROM
activity_status WHERE act_item_descript = 'dataset_info' AND item_id =
114555365 ))

Now it should be right....

On Tue, Aug 2, 2016 at 11:08 AM, Fernando Nunes <domusonline@gmail.com>
wrote:

> I find it a bit hard to understand why it considers an INDEX PATH for the
> second table. A dbschema -hd would be nice, but it won't change my thoughts
> I suppose.
> I may be missing something, but in any case the most time the query takes
> seems to be on the first sub-query.
> The schema for the first table would help also.
>
> But more important (you could open a PMR to investigate if this plan is
> "correct") is the fact that the query is inefficient because of the way
> it's written.
> At the top most level of the query, you have a condition on item_id
> (item_id = VALUE AND item_id IN ... )
> The optimizer is not smart enough to push down that conditions into the
> sub-queries....
>
> Your query is:
>
> SELECT
>
> count(*)
> FROM
>
> dataset_info
> WHERE
>
> item_id=114555365 and
>
> (item_id IN ( SELECT
>
> item_id
>
> FROM
>
> dataset_info
>
> WHERE
>
> path_status='NEWPATH' OR
>
> item_id IN (
>
> SELECT
>
> item_id
>
> FROM
>
> activity_status
>
> WHERE
>
> act_item_descript='dataset_info'
>
> )
>
> )
>
> )
>
> This should be equivalent and better:
>
> SELECT
>
> count(*)
> FROM
>
> dataset_info
> WHERE
>
> item_id=114555365 AND path_status = 'NEWPATH' AND EXISTS (SELECT 1 FROM
> activity_status WHERE act_item_descript = 'dataset_info' AND item_id =
> 114555365 )
>
> Please check.
> Regards
>
> On Mon, Aug 1, 2016 at 8:46 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Thanks Fernando!
> >
> > I updated the table statistics high and rerun the SQL job( a known
> > bad inefficient SQL job). The following is the detailed info.
> >
> > You can see , it used index with SKIP SCAN on column act_item_descript ,
> >
> > informix.activity_status: INDEX PATH (SKIP SCAN)
> >
> > (1) Index Name: informix.activity_status_idx4
> >
> > Index Keys: act_item_descript (Serial, fragments: ALL)
> >
> > Lower Index Filter:
> > informix.activity_status.act_item_descript = 'dataset_info'
> >
> > Which has 99% the same value ! ( 42147 vs 42150)
> >
> > Thanks
> > Frank
> >
> > 1) IDS Version:
> > 12.10.FC4W1XU
> >
> > 2) Table schema:
> > { TABLE "informix".activity_status row size = 304 number of columns = 21
> > index size = 107 }
> > create table "informix".activity_status
> > (
> >
> > status_id serial not null ,
> >
> > activity_id integer not null ,
> >
> > proc_cmd char(30) not null ,
> >
> > processing_path char(20) not null ,
> >
> > act_item_descript char(20) not null ,
> >
> > item_id integer not null ,
> >
> > act_start_dt datetime year to second,
> >
> > activity_stage char(20) not null ,
> >
> > activity_result char(100),
> >
> > queue_number integer not null ,
> >
> > creation_dt datetime year to second,
> >
> > stage_trans_dt datetime year to second,
> >
> > cycle_check_dt datetime year to second,
> >
> > inactivity_count smallint,
> >
> > act_hold_end_dt datetime year to second,
> >
> > path_counter integer,
> >
> > parent_act_itm_dsc char(20),
> >
> > parent_item_id integer,
> >
> > parent_path_name char(20),
> >
> > parent_path_cntr integer,
> >
> > session_id integer,
> >
> > primary key (status_id) constraint "informix".activity_status_pk
> > ) in dbdata01 extent size 20000 next size 4000 lock mode row;
> > revoke all on "informix".activity_status from "public" as "informix";
> >
> > create index "informix".activity_sta_idx1 on "informix".activity_status
> >
> > (activity_stage,status_id) using btree in dbdata00;
> > create index "informix".activity_sta_idx2 on "informix".activity_status
> >
> > (item_id) using btree in dbdata01;
> > create index "informix".activity_status_idx4 on
> "informix".activity_status
> >
> > (act_item_descript) using btree in dbdata01;
> > create index "informix".activity_status_idx5 on
> "informix".activity_status
> >
> > (proc_cmd) using btree in dbdata01;
> >
> > 3) Table data info:
> >
> > select count(*) from activity_status
> >
> > (count(*)) 42150
> >
> > select act_item_descript,count(*)
> > from activity_status
> > group by act_item_descript
> > order by act_item_descript
> >
> > act_item_descript (count(*))
> > dataset_info 42147
> > file_receipt_info 2
> > order_spec 1
> >
> > 4) SQL
> >
> > SELECT count(*) FROM dataset_info WHERE item_id=114555365 and (item_id IN
> >
> > (SELECT item_id FROM dataset_info WHERE path_status='NEWPATH' OR item_id
> >
> > IN (SELECT item_id FROM activity_status WHERE
> > act_item_descript='dataset_info')))
> >
> > 5) Plan explanation
> >
> > Estimated Cost: 30918
> > Estimated # of Rows Returned: 1
> > 1) informix.dataset_info: INDEX PATH
> >
> > (1) Index Name: informix. 270_608
> >
> > Index Keys: item_id (Key-Only) (Serial, fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.item_id = 114555365
> >
> > Index Key Filters: (informix.dataset_info.item_id = ANY <subquery>
> > )
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 30917
> >
> > Estimated # of Rows Returned: 26640
> >
> > 1) informix.dataset_info: INDEX PATH
> >
> > (1) Index Name: informix.dataset_info_idx1
> >
> > Index Keys: path_status item_id (Key-Only) (Serial,
> > fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.path_status =
> > 'NEWPATH'
> >
> > (2) Index Name: informix. 270_608
> >
> > Index Keys: item_id (Serial, fragments: ALL)
> >
> > Lower Index Filter: informix.dataset_info.item_id = ANY
> > <subquery>
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 23317
> >
> > Estimated # of Rows Returned: 42147
> >
> > 1) informix.activity_status: INDEX PATH (SKIP SCAN)
> >
> > (1) Index Name: informix.activity_status_idx4
> >
> > Index Keys: act_item_descript (Serial, fragments: ALL)
> >
> > Lower Index Filter:
> > informix.activity_status.act_item_descript = 'dataset_info'
> >
> > Query statistics:
> > -----------------
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 dataset_info
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 1 1 1 00:01.16 30918
> > type rows_prod est_rows rows_cons time
> > -------------------------------------------------
> > group 1 1 1 00:01.16
> >
> > Subquery statistics:
> > --------------------
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 dataset_info
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 42132 26640 42132 00:00.91 30917
> >
> > Subquery statistics:
> > --------------------
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 activity_status
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 42147 42147 42147 00:00.10 23318
> > type rows_sort est_rows rows_cons time
> > -------------------------------------------------
> > sort 42131 0 42147 00:00.13
> >
> > On Fri, Jul 29, 2016 at 5:29 AM, Fernando Nunes <domusonline@gmail.com>
> > wrote:
> >
> > > On Fri, Jul 29, 2016 at 12:26 AM, FRANK <yunyaoqu@gmail.com> wrote:
> > >
> > > > Thanks Fernando and Art for the info!
> > > >
> > > > So, when SKIP SCAN is used with an index , it normally means it would
> > > > return large percent of the table data , right? ( I checked the
> index,
> > > > seems not an efficient one, it would return more than 90% of the
> rows.
> > > > I might suggest to drop this inefficient index next if possible).
> > > >
> > >
> > > Roughly yes, although for 90% it's highly debatable if it should use
> the
> > > index versus a full scan.
> > > I'm used to see the engine not use INDEX SKIP SCAN when it
> should/could.
> > > This case is the opposite.
> > > I'd suggest you check if your statistics are up to date...
> > >
> > > >
> > > > Another question, I am not sure what I need do with the
> > > > following "workaround" ,
> > > > SELECT a.* FROM test_data a, (SELECT rowid r FROM test_data c WHERE
> > col1
> > > > BETWEEN 1000 AND 1400 ORDER BY 1) b
> > > >
> > >
> > > Nothing... This could be used in previous versions, when an INDEX SKIP
> > SCAN
> > > would be good but not implemented.
> > > It illustrates (roughly) what the engine does.
> > >
> > > >
> > > > Thanks
> > > > Frank
> > > >
> > > > On Thu, Jul 28, 2016 at 12:49 PM, Fernando Nunes <
> > domusonline@gmail.com>
> > > > wrote:
> > > >
> > > > > Yes.... The name is a bit confusing because it came from another
> > > feature
> > > > > (star schema optimized joins).
> > > > > SKIP SCAN means it will "order" the rowIDs it gets from the INDEX
> > prior
> > > > to
> > > > > access the data pages.
> > > > > The advantae is that the accesses to the data pages will tend to be
> > > more
> > > > > "sequential".
> > > > >
> > > > > Believe it or not, the effect can be dramatic when large number of
> > rows
> > > > > match the index condition.
> > > > >
> > > > > A few years ago (before that feature was introduced) I had a test
> > case
> > > > > showing how a sequential scan on a 1M rows table was fatser than a
> > > query
> > > > > that retrived 20k rows from the table.
> > > > > After the feature was introduced (11.70.xC1) it became much fatser
> > than
> > > > the
> > > > > sequential scan.
> > > > >
> > > > > A "workaround" on 11.50 was to run something like:
> > > > >
> > > > > SELECT
> > > > > a.*
> > > > > FROM test_data a, (SELECT rowid r FROM test_data c WHERE col1
> BETWEEN
> > > > 1000
> > > > > AND 1400 ORDER BY 1) b
> > > > >
> > > > > On Thu, Jul 28, 2016 at 5:37 PM, FRANK <yunyaoqu@gmail.com> wrote:
> > > > >
> > > > > > IDS 12.10 FC4.
> > > > > >
> > > > > > I have plan explanation of a sub query,
> > > > > >
> > > > > > Subquery:
> > > > > >
> > > > > > ---------
> > > > > >
> > > > > > Estimated Cost: 23317
> > > > > >
> > > > > > Estimated # of Rows Returned: 42147
> > > > > >
> > > > > > 1) informix.activity_status: INDEX PATH (SKIP SCAN)
> > > > > >
> > > > > > (1) Index Name: informix.activity_status_idx4
> > > > > >
> > > > > > Index Keys: act_item_descript (Serial, fragments: ALL)
> > > > > >
> > > > > > Lower Index Filter:
> > > > > > informix.activity_status.act_item_descript = 'dataset_info'
> > > > > >
> > > > > > Can someone give more detail about what the SKIP SCAN exactly
> does
> > ?
> > > > > >
> > > > > > Thanks
> > > > > > Frank
> > > > > >
> > > > > > --94eb2c050a4430fe210538b4c1e3
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > > > >
> > > > > >
> > > > >
> > > > > --
> > > > > Fernando Nunes
> > > > > Portugal
> > > > >
> > > > > http://informix-technology.blogspot.com
> > > > > My email works... but I don't check it frequently...
> > > > >
> > > > > --94eb2c0549548f652f0538b4ebb3
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --001a11352ae07abf1d0538ba7703
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --
> > > Fernando Nunes
> > > Portugal
> > >
> > > http://informix-technology.blogspot.com
> > > My email works... but I don't check it frequently...
> > >
> > > --001a114aa7281bab530538c2de1e
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a11352ae075d5ee053907da2a
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a11419f0e6fbcc4053913e3d3
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--001a1143e3863c90de053915ed29

Messages In This Thread

• SKIP SCAN
  FRANK -- Thursday, 28 July 2016, at 12:37 p.m.
  • Re: SKIP SCAN
    Art Kagel -- Thursday, 28 July 2016, at 12:46 p.m.
  • Re: SKIP SCAN
    Fernando Nunes -- Thursday, 28 July 2016, at 12:49 p.m.
    • Re: SKIP SCAN
      FRANK -- Thursday, 28 July 2016, at 7:26 p.m.
      • Re: SKIP SCAN
        Fernando Nunes -- Friday, 29 July 2016, at 5:29 a.m.
        • Re: SKIP SCAN
          FRANK -- Monday, 1 August 2016, at 3:46 p.m.
          • Re: SKIP SCAN
            Fernando Nunes -- Tuesday, 2 August 2016, at 6:08 a.m.
            • Re: SKIP SCAN
              Fernando Nunes -- Tuesday, 2 August 2016, at 8:34 a.m.
    • Re: SKIP SCAN
      MARK SCRANTON -- Monday, 22 August 2016, at 6:33 p.m.
      • Re: SKIP SCAN
        Fernando Nunes -- Tuesday, 23 August 2016, at 5:17 a.m.
        • Re: SKIP SCAN
          Fernando Nunes -- Tuesday, 23 August 2016, at 5:59 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.