IDS Forum

Re: SKIP SCAN

Posted By: FRANK
Date: Monday, 1 August 2016, at 3:46 p.m.

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

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

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.