IDS Forum

Re: SKIP SCAN

Posted By: Fernando Nunes
Date: Tuesday, 2 August 2016, at 6:08 a.m.

In Response To: Re: SKIP SCAN (FRANK)

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

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.