jump to navigation

Index Join in Oracle and the equivalent Index Intersection in Sybase ASE September 1, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
1 comment so far

Both Oracle and Sybase ASE provide multipole access paths to the optimizer. Many people have this perception that for a given table only one index can be used by the optimizer. Although this is a widely expected outcome and it holds true for most cases, under certain conditions where the optimizer decides that it does not need to access the base table, more than one index on the same table can be used to satisfy the query.

Consider a simple case of a select list returning the values for a column or columns based on columns used in a where caluse.  In olther words the select list is entirely satisfied by the columns in the where clause. Let us look at a simple example in each case

Oracle (11gR2)

First create the table:

create table #generator(id int not null)
go
CREATE TABLE index_joins AS
 SELECT
 ROWNUM AS c1,
 (ROWNUM + trunc(dbms_random.value(0,1000))) AS c2,
 LPAD('A',255,'A') AS c3
 FROM
 DUAL
 CONNECT BY
 LEVEL <=10000;
CREATE UNIQUE INDEX ind_c1 ON index_joins(c1);
CREATE INDEX ind_c2 on index_joins(c2);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=USER,TABNAME='INDEX_JOINS',CASCADE=>TRUE)

Let us query this table to get columns c1 and c2

alter system flush buffer_cache;
alter system flush shared_pool;
set timing on
set autotrace on
SELECT c1, c2
FROM index_joins
where   c1 between 100 and 1000
and     c2 between 50 and 100;

The select list contains column c1 and c2. The index ind_c1 covers c1 column and the index ind_c2 is on c2. These two indexes on index_joins table satisfy the query. Hence there is no need for the optimizer to visit the base table. Let us look at the plan:

---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     4 |    36 |     6  (17)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001 |     4 |    36 |     6  (17)| 00:00:01 |
|*  2 |   HASH JOIN        |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IND_C2           |     4 |    36 |     3  (34)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IND_C1           |     4 |    36 |     4  (25)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter("C2"<=100 AND "C1"<=1000 AND "C1">=100 AND "C2">=50)
 2 - access(ROWID=ROWID)
 3 - access("C2">=50 AND "C2"<=100)
 4 - access("C1">=100 AND "C1"<=1000)

Let us look at the plan outpout. The optimizer does a index range scan of the two indexes extracting the rowid and c1 from index ind_c1 and the rowid and c2 from ind_c2. Then the optimizer performs a hash join between threse two results set on the rowid values. It is doing this because anytime the two rowsources have a rowid in common, that is the interestion that we want and both c1 and c2 values will be sent back as the result of the query.

Sybase ASE (ASE 15.7 ESD 2)

First we create the equivalent table in Sybase

create table #generator(id int not null)
go
create table index_joins
(
c1 int not null
, c2 int not null
, c3 varchar(255)
)
lock datarows
go
CREATE UNIQUE INDEX ind_c1 ON index_joins(c1)
CREATE INDEX ind_c2 on index_joins(c2)
go
--
declare @i int
declare @rows int
set @rows = 100
set @i = 1
set nocount on
while @i <=@rows
begin
 insert into #generator values (@i)
 set @i = @i + 1
end
set nocount off
insert into
 index_joins
select
 rs.id
 , rs.id + abs(rs.random % 1000)
 , REPLICATE('A',255)
from    ( select
 @rows * ( g1.id - 1 ) + g2.id AS id
 , CAST(NEWID() AS VARBINARY)  AS random
 from
 #generator g1,
 #generator g2
 ) rs
go
update index statistics index_joins
go

So that is the equivalent table in Sybase.

Unlike Oracle, the optimizer in Sybase will not consider using two indexes on the same table. The only way one could use two different indexes for the same table in a query is to have an  index intersection plan. Index Intersection is a technique to enable ASE optimizer to use more than one index on a table to satisfy a given query. This should work similar to Oracle “index join” whereby each index satisfies the predicate on the query. The hash join between two indexes results in common Row IDs (RID) to be selected on intersection and base table rows accessed by the intersection RowIDs. In ASE this index interesection has to be enabled explicitly by the following set command:

set index_intersection on

In addition, you need to supply multiscan abstract plan operator m_scan. The query will look like the following code:

set switch on 3604
go
declare @dbname varchar(30)
set @dbname=db_name()
dbcc cachedataremove(@dbname)
go
set statement_cache off
go
set showplan on
set statistics time,io,plancost on
go
set index_intersection on
go
SELECT c1, c2
FROM index_joins
where   c1 between 100 and 1000
and     c2 between 50 and 100
plan '(m_scan index_joins)'
go

The output of the Lava tree operator shows:

==================== Lava Operator Tree ====================
                     Emit
                     (VA = 6)
                     r:0 er:1
                     cpu: 0
                /
              RIDJoin
              (VA = 5)
              r:0 er:1
              l:4 el:7
              p:0 ep:0
     /                                              \
    HashJoin                                     Restrict
    Inner Join                                   (0)(0)(0)(13)(0)
    (VA = 2)                                     (VA = 4)
    r:0 er:1                                     r:0 er:1
    l:4 el:5
    p:0 ep:0
    bufct: 128
   /                      \                        /
IndexScan               IndexScan               TableScan
ind_c2                  ind_c1                  index_joins
(VA = 0)                (VA = 1)                (VA = 3)
r:0 er:1                r:0 er:900              r:0 er:1
l:2 el:2                l:0 el:3                l:0 el:934
p:2 ep:2                p:0 ep:3                p:0 ep:5
============================================================<br />

The execution plan steps are described as follows:

  1. index scan of ind_c2 to get the RIDs of the qualifying rows for “index_joins.c2 between 50 and 100”
  2. index scan of ind_c1 to get the RIDs of the qualifying rows for “t.index_joins.c1 between 100 and 1000”
  3. does HashJoin between those two sets of RIDs to return the common RID
  4. The stream of unique RIDs is passed to the RID JOIN operator. This operator creates a worktable (aka Dynamic Index) and fills it with a single-column row with each RID.
  5. does a RID Scan on the table index_joins using those RIDs from the worktable and gets back the rows.

Note that there is a subtle difference in behaviour between Oracle and ASE. Oracle does not require to visit the base table. However, ASE will visit the base table to feth the result set for RIDs from the worktable created. This method will also work for cases where the result set includes other columns from the table. for example a select * from the base table will also use this access path in ASE.

Use Case Comparison of ORACLE 11gR2 and Sybase ASE 15.7 on Solid State Disks June 2, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
add a comment

Database performance is ultimately constrained by the need to read and write data from a persistent storage. For almost the entire history of Oracle and Sybase, this persistent storage has been provided by the magnetic spinning hard disks (HDD). The relative performance of magnetic disk has been so poor compared to the rest of the hardware, that database performance tuning has focused primarily on minimizing the disk IO. Only now, with the advent of Solid State Disk (SSD) technology and in-memory databases, we are getting to the point of an increase in IO performance to match the improvements we have come to take for granted in CPU and memory access speeds.

Although replacing all the storage component of Oracle and Sybase with SSD is a rather expensive option for many shops, Both databases can benefit from an intelligent use of solid state disks. However, it is not always obvious how best to leverage limited quantities of SSD in order to get the best performance return from the investment.

A client of mine requested me recently to undertake “Use Case Comparison of ORACLE 11G and Sybase ASE 15 “on Solid State Disks. They wanted to improve their system response and both Oracle and Sybase databases were feeding their trading systems. I undertook this study separately for both Oracle 11gR2 and Sybase ASE 15.7 and the outcomes were very interesting. To simplify the matters we separated  read intensive and write intensive activities for both Oracle and Sybase. The results of the studies for read intensive activitiesfor Oracle have now been published in Oracle SELECT journal. SELECT Journal is a quarterly journal of peer-reviewed technical articles and news published by the Independent Oracle Users Group (IOUG). This article was published under title “Use Case Comparison of ORACLE 11g on Solid State Disks (Part One)” . The result of studies for write intensive activities for Oracle 11g will be published shortly

Likewise, the result of studies for read intensive activities for Sybase ASE 15 was published in International Sybase User Group (ISUG) journal in April 2012 edition titled “Use Case Comparison of ASE 15.7 on Solid State Disks [Part 1]“. The result of studies for write intensive activitiesfor Sybase ASE 15 have now been published in June 2012 edition of ISUG journal titled “Use Case Comparison of ASE 15.7 on Solid State Disks [Part 2]“.

A user defined function for Sybase ASE to emulate Oracle’s dbms_random.string() function May 31, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
add a comment

In Oracle, dbms_random package allows one to generate random data. A very widely used function within this package is dbms_random.string(opt, len) that returns a random string whose length is determined by the len argument.

The returned string’s characteristics are determined by the opt argument. opt must be a char. The following five options are supported:
‘u’ or ‘U’: Upper case alpha characters
‘l’ or ‘L’: Lower case alpha characters
‘a’ or ‘A’: Upper and lower case alpha characters
‘x’ or ‘X’: Upper alpha and numeric characters
‘p’ or ‘P’: Any printable character

An example calling the above function with different options would be

col u format a10
col l format a10
col a format a10
col x format a10
col p format a10

select
2    dbms_random.string('u', 10) as u,
3    dbms_random.string('l', 10) as l,
4    dbms_random.string('a', 10) as a,
5    dbms_random.string('x', 10) as x,
6    dbms_random.string('p', 10) as p
7  from
8    dual;
U          L          A          X          P
---------- ---------- ---------- ---------- ----------
PSXPJUOMMT byebvyevlb JPRjgoAxOb QM4XU14IPX [`i06!e8m0

In Sybase ASE this random string function does not exist. The problem is that even if you write your own user defined function (UDF) it won’t work. There is currently an issue with calling non deterministic functions (getdate(),rand, rand2, newid(), etc) directly from within an ASE UDF. Basically if I try to do the following:

create function sf_test(@dummy int)
returns int
as
begin
  declare @v int
  select @v = rand2()  -- calling rand function here
  return @v
end

I will get the following error:

Msg 11798, Level 15, State 65:
Illegal BUILTIN FUNCTION statement within a scalar SQL function.

The work around is to define these functions in a view and call them in UDF referencing the view (thus avoiding direct references to these functions in UDF).

For example create the view in sybsystemprocs first

create view vudf
as
select
  CHAR(ASCII('A')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaU
, CHAR(ASCII('a')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaL
, CONVERT(char(1),ABS(CAST(NEWID() AS VARBINARY) %9)) as vrandnum
, CHAR(ASCII('!')+ABS(CAST(NEWID() AS VARBINARY) %95))as vprintable
, FLOOR(RAND2()*100) as vrand
, GETDATE() as vgetdate

This is not ideal but as a work around it can be expanded to provide all the non deterministic functions required. The overhead is maintaining this view in sybsystemprocs. An example calling getdate in udf:

create function sf_getdate(@datein datetime)
returns int
as
begin
  declare @diff int
  select @diff = datediff(day,@datein,vgetdate)from sybsystemprocs.dbo.vudf
  return @diff
end
go
-- run this
select dbo.sf_getdate('Apr 15 2012')
go
-----------
46

Similar to above and using the view generated we can write a random string function in ASE:

use sybsystemprocs
go
if exists(select 1 from sysobjects where type = 'V' and name = 'vudf')
drop view vudf
go
create view vudf
as
select
  CHAR(ASCII('A')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaU
, CHAR(ASCII('a')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaL
, CONVERT(char(1),ABS(CAST(NEWID() AS VARBINARY) %9)) as vrandnum
, CHAR(ASCII('!')+ABS(CAST(NEWID() AS VARBINARY) %95))as vprintable
, FLOOR(RAND2()*100) as vrand
, GETDATE() as vgetdate
go
if exists (select 1 from sysobjects where type = 'SF' and name = 'sf__randalpha')
drop function sf__randalpha
go
create function sf__randalpha (@opt char(1),@len int)
returns varchar(255)
as
/******************************************************************************
**
** Name        : sf__randalpha
**
** Created By  : Mich Talebzadeh
** Date        : 30/05/2012
** Purpose     : The function string returns a string whose length is determined by the len argument.
**               The returned string's characteristics are determined by the opt
**               argument. opt must be a char. The following five options are possible:
**               'u' or 'U': Upper case alpha characters
**               'l' or 'L': Lower case alpha characters
**               'a' or 'A': Upper and lower case alpha characters
**               'x' or 'X': Upper alpha and numeric characters
**               'p' or 'P': Any printable character
**
**               Examples.
**               declare @c varchar(30)
**               select @c = sybsystemprocs.dbo.sf__randalpha('a',30)
**
******************************************************************************/
begin
  if UPPER(@opt) NOT IN ('U','L','A','X','P') return null
  if (@len <= 0) return null
  declare @randalpha varchar(255)
  declare @counter int
  declare @valpha char(1)
  declare @i int
  set @randalpha = null
  set @counter = 1
  while (@counter <= @len)
  begin
    if UPPER(@opt)='U' select @valpha = valphaU from sybsystemprocs.dbo.vudf
    if UPPER(@opt)='L' select @valpha = valphaL from sybsystemprocs.dbo.vudf
    if UPPER(@opt)='P' select @valpha = vprintable from sybsystemprocs.dbo.vudf
    if UPPER(@opt) = 'A'
    begin
      select @i = vrand from sybsystemprocs.dbo.vudf
      if @i < 50 begin select @valpha = valphaU from sybsystemprocs.dbo.vudf end else begin select @valpha = valphaL from sybsystemprocs.dbo.vudf end
    end
    if UPPER(@opt) = 'X'
    begin
     select @i = vrand from sybsystemprocs.dbo.vudf
     if @i < 50 begin select @valpha = valphaU from sybsystemprocs.dbo.vudf end else begin select @valpha = vrandnum from sybsystemprocs.dbo.vudf end
    end
    select @randalpha = @randalpha || @valpha
    select @counter = @counter + 1
  end
  return substring(@randalpha,1,@len)
end
go
grant exec on dbo.sf__randalpha to public
go
sp_help sf__randalpha
go

Now if I use the above function I can create all the random strings as per Oracle

1> select
2>   substring(sybsystemprocs.dbo.sf__randalpha('u',10),1,10) as u,
3>   substring(sybsystemprocs.dbo.sf__randalpha('l',10),1,10) as l,
4>   substring(sybsystemprocs.dbo.sf__randalpha('a',10),1,10) as a,
5>   substring(sybsystemprocs.dbo.sf__randalpha('x',10),1,10) as x,
6>   substring(sybsystemprocs.dbo.sf__randalpha('p',10),1,10) as p
7> go
u          l          a          x          p
---------- ---------- ---------- ---------- ----------
GEMIGRGZHV sxttmcwujw nNxJBYefqa W5K2NTHG67 oi'8e&8{GV

Creating almost identical test tables in Oracle and Sybase March 27, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
add a comment

A question as below was put in one of the forums and I quote:

“I would like to create an equivalent of the following [Oracle] test data in Sybase ASE. I did a search for generator in ASE but it does not seem to exist. This code generates 25 rows of test data in Oracle 11gR2:


CREATE TABLE dummy2
 AS
 WITH GENERATOR AS (
 SELECT            rownum            id
 FROM        dual
 CONNECT BY
 rownum &lt;= 5
 )
 SELECT
 rownum                                          id
 , trunc((rownum - 1) / 1000)                clustered
 , mod(rownum - 1,1000)                      scattered
 , trunc(dbms_random.value(0,1000))          randomised
 , substr(dbms_random.string('A',15),1,15)   random_string
 , lpad(rownum,10)                           rownumchar
 , rpad('x',10,'x')                          xchar
 FROM
 generator   g1,
 generator   g2
 ;
 ALTER TABLE dummy2 ADD CONSTRAINT dummy2_pk PRIMARY KEY(id);

FYI, this will generate 25 rows of data which the first few rows will look like:


ID  CLUSTERED  SCATTERED RANDOMISED RANDOM_STRING   ROWNUMCHAR XCHAR
 ---------- ---------- ---------- ---------- --------------- ---------- ----------
 1          0          0        968 NrsHaXarDZNFwkh          1 xxxxxxxxxx
 2          0          1        194 tJFaWzmfEEhCmHr          2 xxxxxxxxxx
 3          0          2        159 tVRDLHAaqIAAJgz          3 xxxxxxxxxx
 4          0          3        912 XPmeibdSvaKMfSz          4 xxxxxxxxxx
 5          0          4        949 ufIHshljSFagfla          5 xxxxxxxxxx
 6          0          5        542 TUhUiVapvbPqZzY          6 xxxxxxxxxx
 7          0          6        710 nIHBNkeqVNKNNEu          7 xxxxxxxxxx
 8          0          7        297 urhNnamqGXyRgJU          8 xxxxxxxxxx
 9          0          8        123 OihsWFSphsivZVE          9 xxxxxxxxxx
 10          0          9        810 SwENZKbugGTuuyw         10 xxxxxxxxxx

I know few things. For example for rownum it may be possible to use identity in ASE. Other functions I can find out for myself. the SQL does a cartesian join using the generator.”

OK fair enough. Now my answer to the question

Sybase ASE does not have a constructor like generator. However, that is no big deal. As ever we can use a temporary table in ASE (a #table) to create a cartesian join.

So let us try this

--
--- create the temporary generator table here, one column increasing number bigint
--
create table #generator(id bigint not null)
go
--
-- And your dummy2 table here
--
create table dummy2
(
id bigint not null
, clustered1 int   -- Note clustered is a reserved word in ASE
, scattered int
, randomised int
, random_string varchar(15)
, rownumchar varchar(10)
, xhar varchar(10)
)
go
ALTER TABLE dummy2 ADD CONSTRAINT dummy2_pk PRIMARY KEY(id)
go
--
-- Now simply populate generator in a while loop
--
declare @i bigint
declare @rows bigint
set @rows = 5  -- number of rows in generator
set @i = 1
while @i &lt;=@rows
begin
  insert into #generator values (@i)
  set @i = @i + 1
end
insert into
dummy2
select
rs.id
, floor(rs.id - 1)/1000
, abs(rs.id - 1 % 1000)
, abs(rs.random % 1000)
, password_random(15)
, right(replicate(&quot; &quot;,10) + convert(varchar,id), 10)
, REPLICATE('x',10)
from    ( select
@rows * ( g1.id - 1 ) + g2.id AS id    -- This will create incrementing values without the use of identity column
, CAST(NEWID() AS VARBINARY)  AS random  -- prefer this one to rand which is seed dependent etc
from
#generator g1,
#generator g2
) rs
go

It should do something the OP wanted.


id                   clustered1  scattered   randomised  random_string   rownumchar xhar
-------------------- ----------- ----------- ----------- --------------- ---------- ----------
1           0           0         991 U)[u5{2U74Pw_RT          1 xxxxxxxxxx
2           0           1         760 qVn`S8^7YVqI-;P          2 xxxxxxxxxx
3           0           2         425 iv/c`Rd&amp;Pa4-qqF          3 xxxxxxxxxx
4           0           3         422 c.uEslwyji5K&lt;-j          4 xxxxxxxxxx
5           0           4         589 :(Z~uFZpJ`r%3Lx          5 xxxxxxxxxx
6           0           5         153 K]cwplm*;iYjRX#          6 xxxxxxxxxx
7           0           6         755 }iPKeG|^s?%9hi\          7 xxxxxxxxxx
8           0           7          87 v/+aL,M$H&lt;rXJTZ          8 xxxxxxxxxx
9           0           8         988 S;ZUY.ZWG)1mg%$          9 xxxxxxxxxx
10           0           9         216 ,tLD|X;(*KCB3&gt;&amp;         10 xxxxxxxxxx
11           0          10         581 U!zQ&gt;;InB_b&gt;!GA         11 xxxxxxxxxx
12           0          11         938 r8B}ewup^;ivn1*         12 xxxxxxxxxx
13           0          12         620 :RhXb8.&gt;K_-Q{W1         13 xxxxxxxxxx
14           0          13         602 GE,a3V$(M6FU08/         14 xxxxxxxxxx
15           0          14         186 YHIlB.C$}~Gvz?]         15 xxxxxxxxxx
16           0          15         740 )t&gt;R~N9r;4C{G[A         16 xxxxxxxxxx
17           0          16         486 E%+@3n;6hv2E%34         17 xxxxxxxxxx
18           0          17           9 c6sX&amp;rl;{wXXmdO         18 xxxxxxxxxx
19           0          18         897 lld~Au+GwFWyA!i         19 xxxxxxxxxx
20           0          19         350 Nu@(OEPJ1!*5iY#         20 xxxxxxxxxx
21           0          20         155 )Zloy!MPX0n?%*a         21 xxxxxxxxxx
22           0          21          44 S6ouhI=]$Y?=4Kv         22 xxxxxxxxxx
23           0          22         510 w:^z@Ub*S1ykP?x         23 xxxxxxxxxx
24           0          23         613 #j{2_Fyz&lt;*Wzsn/         24 xxxxxxxxxx
25           0          24         917 4B/-JK.Dia&gt;yi3\         25 xxxxxxxxxx

(25 rows affected)

Obviously that random string is not exactly what was created in the Oracle code ( dbms_random.string(‘A’, 15), i.e. upper and lower case alpha characters ). password_random function in ASE generates something like dbms_random.string(‘P’, 15)  meaning ‘any printable character’.

Knowing one database and learning another March 27, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
add a comment

Many people ask me the easiest way of learning another database based on your current knowledge. For example how to learn Oracle knowing Sybase ASE. I am sure many people have been through that route.

Well it is always natural. Oracle looks harder to someone because he/she  “thinks” in Sybase. Sybase may look hard to someone else because he/she “thinks” in Oracle.

In general in order to overcome the learning barrier you need to accept the fact that these two databases do many thinks in common and some of the stuff differently. My suggestion is to try to avoid interpreting the way things are done in say ASE and do not bring your opinion (read bias) into it. Locking, concurrency, Oracle MVRC and other stuff are different in these two engines and the best way to learn is to accept them as they are and not interpreting them.

In general most RDBMSs can achieve the same thing in different ways. Knowing one database and learning another is great in that you are familiar with the whole theory and all you need to know is the implementation and syntax.

Also bear in mind that there are architectural differences between ASE and Oracle. Oracle uses process pool model and ASE is Multi-threaded. That makes things slightly more complicated.