Oracle to postgresql

[bangalore umesh]

Hi

Please help me with document for oracle to postgresql
useing Ora2pgtool

-- 
Thanks and Regards
Rajshekhariah Umesh

Re: Oracle to postgresql

[Ian Lawrence Barwick]

2020年12月18日(金) 19:43 bangalore umesh :
>
> Hi
>
> Please help me with document for oracle to postgresql
> useing Ora2pgtool

AFAIK the main documentation is the extensive README file, as available here:

  https://github.com/darold/ora2pg

Regards

Ian Barwick

Reversing NULLS in ORDER causes index not to be used?

[Ken Tanzer]

Hi.  I'm wondering if this is normal or at least known behavior?
Basically, if I'm specifying a LIMIT and also  NULLS FIRST (or NULLS LAST
with a descending sort), I get a sequence scan and a couple of orders of
magnitude slower query.  Perhaps not relevantly, but definitely ironically,
the sort field in question is defined to be NOT NULL.

This is on 9.6.20.  I tried a couple of different tables in a couple of
databases, with similar results.

Thanks in advance for any insight!

Ken


=> EXPLAIN ANALYZE SELECT * FROM tbl_entry WHERE NOT is_deleted ORDER BY
entered_at NULLS LAST LIMIT 60;
  QUERY
PLAN
--
 Limit  (cost=0.29..2.78 rows=60 width=143) (actual time=0.027..0.260
rows=60 loops=1)
   ->  Index Scan using index_tbl_entry_entered_at on tbl_entry
 (cost=0.29..4075.89 rows=98443 width=143) (actual time=0.023..0.105
rows=60 loops=1)
 Planning time: 0.201 ms

* Execution time: 0.366 ms*(4 rows)

=> EXPLAIN ANALYZE SELECT * FROM tbl_entry WHERE NOT is_deleted ORDER BY
entered_at NULLS FIRST LIMIT 60;
  QUERY PLAN

--
 Limit  (cost=5927.55..5927.70 rows=60 width=143) (actual
time=269.088..269.302 rows=60 loops=1)
   ->  Sort  (cost=5927.55..6173.65 rows=98443 width=143) (actual
time=269.085..269.157 rows=60 loops=1)
 Sort Key: entered_at NULLS FIRST
 Sort Method: top-N heapsort  Memory: 33kB
 ->  Seq Scan on tbl_entry  (cost=0.00..2527.87 rows=98443
width=143) (actual time=0.018..137.028 rows=98107 loops=1)
   Filter: (NOT is_deleted)
   Rows Removed by Filter: 1074
 Planning time: 0.209 ms
 *Execution time: 269.423 ms*
(9 rows)

=> \d tbl_entry
  Table "public.tbl_entry"
   Column|  Type  |
 Modifiers
-++--
 entry_id| bigint | not null default
nextval('tbl_entry_entry_id_seq'::regclass)
 entered_at  | timestamp without time zone| not null
 exited_at   | timestamp without time zone|
 client_id   | integer| not null
 issue_no| integer|
 source  | character(1)   |
 entry_location_code | character varying(10)  | not null
 added_by| integer| not null default
sys_user()
 added_at| timestamp(0) without time zone | not null default
now()
 changed_by  | integer| not null default
sys_user()
 changed_at  | timestamp(0) without time zone | not null default
now()
 is_deleted  | boolean| not null default
false
 deleted_at  | timestamp(0) without time zone |
 deleted_by  | integer|
 deleted_comment | text   |
 sys_log | text   |
Indexes:
"tbl_entry_pkey" PRIMARY KEY, btree (entry_id)
"index_tbl_entry_client_id" btree (client_id) WHERE NOT is_deleted
"index_tbl_entry_client_id_entered_at" btree (client_id, entered_at)
WHERE NOT is_deleted
"index_tbl_entry_entered_at" btree (entered_at) WHERE NOT is_deleted
"index_tbl_entry_entry_location_code" btree (entry_location_code) WHERE
NOT is_deleted
"index_tbl_entry_is_deleted" btree (is_deleted)
Check constraints:
"tbl_entry_check" CHECK (NOT is_deleted AND deleted_at IS NULL OR
is_deleted AND deleted_at IS NOT NULL)
"tbl_entry_check1" CHECK (NOT is_deleted AND deleted_by IS NULL OR
is_deleted AND deleted_by IS NOT NULL)
Foreign-key constraints:
"tbl_entry_added_by_fkey" FOREIGN KEY (added_by) REFERENCES
tbl_staff(staff_id)
"tbl_entry_changed_by_fkey" FOREIGN KEY (changed_by) REFERENCES
tbl_staff(staff_id)
"tbl_entry_client_id_fkey" FOREIGN KEY (client_id) REFERENCES
tbl_client(client_id)
"tbl_entry_deleted_by_fkey" FOREIGN KEY (deleted_by) REFERENCES
tbl_staff(staff_id)
"tbl_entry_entry_location_code_fkey" FOREIGN KEY (entry_location_code)
REFERENCES tbl_l_entry_location(entry_location_code)
Triggers:
tbl_entry_alert_notify AFTER INSERT OR DELETE OR UPDATE ON tbl_entry
FOR EACH ROW EXECUTE PROCEDURE table_alert_notify()
tbl_entry_log_chg AFTER DELETE OR UPDATE ON tbl_entry FOR EACH ROW
EXECUTE PROCEDURE table_log()



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client

Re: Reversing NULLS in ORDER causes index not to be used?

[Tom Lane]

Ken Tanzer  writes:
> Hi.  I'm wondering if this is normal or at least known behavior?
> Basically, if I'm specifying a LIMIT and also  NULLS FIRST (or NULLS LAST
> with a descending sort), I get a sequence scan and a couple of orders of
> magnitude slower query.  Perhaps not relevantly, but definitely ironically,
> the sort field in question is defined to be NOT NULL.

The index won't get credit for matching the requested ordering if it's
got the wrong null-ordering polarity.  There's not an exception for
NOT NULL columns.  If you know the column hasn't got nulls, why are
you bothering with a nondefault null-ordering request?

regards, tom lane

Re: Reversing NULLS in ORDER causes index not to be used?

[Ken Tanzer]

On Fri, Dec 18, 2020 at 6:03 PM Tom Lane  wrote:

> Ken Tanzer  writes:
> > Hi.  I'm wondering if this is normal or at least known behavior?
> > Basically, if I'm specifying a LIMIT and also  NULLS FIRST (or NULLS LAST
> > with a descending sort), I get a sequence scan and a couple of orders of
> > magnitude slower query.  Perhaps not relevantly, but definitely
> ironically,
> > the sort field in question is defined to be NOT NULL.
>
> The index won't get credit for matching the requested ordering if it's
> got the wrong null-ordering polarity.  There's not an exception for
> NOT NULL columns.  If you know the column hasn't got nulls, why are
> you bothering with a nondefault null-ordering request?
>
>
I didn't write the query.  I was just trying to troubleshoot one (an d not
the one I sent--that was a simplified example).  In this case it didn't
matter.  It just hadn't ever occurred to me that NULLS FIRST/LAST could
have performance impacts, and I couldn't see why.

I also see now that CREATE INDEX has NULLS FIRST/LAST options, which now
makes perfect sense but was news to me.

Still though is there no optimization gain to be had for being able to
handle nulls either first or last in an index?  I blissfully know nothing
about how such things _actually_ work, but since they're all together at
either the beginning or the end, it seems like there'd be at most one skip
in the order of the values to account for, which seems like in many cases
would be better than not using an index at all.  But there's probably good
reasons why that doesn't hold water. :)

Thanks!

Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.