More on Bitmaps

Bitmap and other indexeing

Bitmaps and reverse indexes

Found a good source of info on stuff I have not fully understood before

reverse indexes

bitmap myths

Star transformations

https://blogs.oracle.com/optimizer/entry/star_transformation

sql developer 4 - ubuntu crahses

The answer turned out to be

ulimit -c unlimited
followed by
unset GNOME_DESKTOP_SESSION_ID

then ./sqldeveloper.sh

https://forum.java.sun.com/thread/260563

materialized view - this article sums up my view of Materialized views

http://oraclesponge.wordpress.com/2006/04/12/a-quick-materialized-view-performance-note/

sql developer CSV hint

Outputs a csv . There are others too.

Tom again

http://www.oracle.com/technetwork/issue-archive/2013/13-jul/o43asktom-1958803.html

More on flashback

flashback table t
to before drop;

NULLs are a bad idea use 'UNKNOWN' cos it gets indexed - another Myth!

I have thought this for a long time and sure enough Tom explains.

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html

Datapump directly from PL/sql

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

DECLARE
  l_dp_handle       NUMBER;
  l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  l_job_state       VARCHAR2(30) := 'UNDEFINED';
  l_sts             KU$_STATUS;
BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    remote_link => NULL,
    job_name    => 'EMP_EXPORT',
    version     => 'LATEST');

  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.dmp',
    directory => 'TEST_DIR');

  DBMS_DATAPUMP.add_file(
    handle    => l_dp_handle,
    filename  => 'SCOTT.log',
    directory => 'TEST_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

  DBMS_DATAPUMP.metadata_filter(
    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''SCOTT''');

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/

Reading XML from database

Can use sqlloader
or

/*

<?xml version="1.0" encoding="UTF-8"?>

<INCOME_ACCESS_ROOT_SALES>

        <INCOME_ACCESS_CHILD_SALES>

                <TRANSACTION_DATE>20140401</TRANSACTION_DATE>

                <BTAG>a_9947b_5033c_GALA_UK_BINGO_B10G50_2565_LLK_GALBLLKBOTH_iPhone_Female_LinkP_BingoFlo40Free_CPC</BTAG>

        </INCOME_ACCESS_CHILD_SALES>

</INCOME_ACCESS_ROOT_SALES>

*/

select

    extractvalue(column_value,'/INCOME_ACCESS_ROOT_SALES/INCOME_ACCESS_CHILD_SALES/TRANSACTION_DATE'),

    extractvalue(column_value,'/INCOME_ACCESS_ROOT_SALES/INCOME_ACCESS_CHILD_SALES/BTAG')

  from

    table(xmlsequence(xmltype(bfilename('JDE','IGB_SALES_TEST.xml'),

      nls_charset_id('WE8ISO8859P1'))));

ODI Studio connection file

c:\users\richard.hall\AppData\Roaming\odi\oracleodi\snps_login_work.xml

or search for snps_login_work.xml