Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick Smile

I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming Smile) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.


SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') x from dual

X
-------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
 (    "X" NUMBER(*,0)
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (  
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO"
PARTITION BY LIST ("X")
(PARTITION "P1"  VALUES (1) 
   SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   STORAGE (
     INITIAL 8388608 
     NEXT 1048576 
     MINEXTENTS 1 
     MAXEXTENTS 2147483645  
     PCTINCREASE 0 
     FREELISTS 1 
     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT 
     FLASH_CACHE DEFAULT 
     CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ,
 PARTITION "P2"  VALUES (2) 
   SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   STORAGE (
     INITIAL 8388608 
     NEXT 1048576 
     MINEXTENTS 1 
     MAXEXTENTS 2147483645  
     PCTINCREASE 0 
     FREELISTS 1 
     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT 
     FLASH_CACHE DEFAULT 
     CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "LARGETS" )

With a little colour coding, you can see that there are three tablespaces that pertain to this table:

  • DEMO
  • USERS
  • LARGETS

But look what happens when I query the data dictionary for those tablespaces:


SQL> select tablespace_name
  2  from   dba_tablespaces
  3  where  tablespace_name in ('DEMO','USERS','LARGETS');

TABLESPACE_NAME
------------------------
LARGETS
USERS

Where is DEMO? Where has it gone? More startlingly, how can I have an existing table that needs that tablespace, and yet the tablespace is not present in the database? Have I lost data? Is there corruption?

Fortunately, the answer to all of these questions do not involve data loss and/or corruption. It is a quirk of the syntax that can be used for partitioned tables. Here is the DDL as I wrote it for the table T.


SQL> create table t ( x int ) tablespace demo
  2  partition by list ( x )
  3  ( partition p1 values (1) tablespace users,
  4    partition p2 values (2) tablespace largets
  5  );

Table created.

And immediately after I created the table, I did the following


SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

You might be thinking that such an operation would surely drop the table I just created as well, but it is still here just fine.


SQL> desc t
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------
 X                                                                          NUMBER(38)

The specification of the tablespace at table level for a partitioned table is nominating the default tablespace for each partition in case it is not specified at partition level. Because I specified a tablespace explicitly for the two partitions on T, the tablespace DEMO does not contain any data, or any partitions for that matter. Which is why I was able to drop it without any problems. Compounding the confusion that often arises is that you won’t see the tablespace name DEMO listed in the USER_TABLES dictionary view even if I had not dropped the tablespace.


SQL> select tablespace_name from user_tables
  2  where table_name = 'T';

TABLESPACE_NAME
------------------------
(null)

The tablespace name in USER_TABLES nominates the tablespace for the segment that will be associated with this table. You will see a similar null value in this column when the table is an Index Organized Table, because it is the underlying index that maps to a tablespace, not the table definition. For a partitioned table, to see which tablespace is the default tablespace, you need to query the USER_PART_TABLES dictionary view.


SQL> select def_tablespace_name
  2  from user_part_tables
  3  where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------
DEMO

Besides this being some trickery with tablespace definitions, there is a good reason why you should know about the segments tablespace and the default tablespace for partitioned objects. As it stands, I could run a datapump export command on the table T and it will successfully be unloaded to a data pump file. However, if I attempt to run a data pump import, the creation of the table will fail, because of the (now illegal) reference to the DEMO tablespace.

So if you are planning to run a data pump export, here’s a query I whipped up to run a check against your database to ensure that you don’t have any references to tablespaces that no longer exist in your database.


SQL> with all_possible_ts as
  2  (
  3  select tablespace_name from dba_lobs                       union all
  4  select tablespace_name from dba_clusters                   union all
  5  select tablespace_name from dba_indexes                    union all
  6  select tablespace_name from dba_rollback_segs              union all
  7  select tablespace_name from dba_tables                     union all
  8  select tablespace_name from dba_object_tables              union all
  9  select def_tablespace_name from dba_part_tables            union all
 10  select def_tablespace_name from dba_part_indexes           union all
 11  select tablespace_name from dba_tab_partitions             union all
 12  select tablespace_name from dba_ind_partitions             union all
 13  select tablespace_name from dba_tab_subpartitions          union all
 14  select tablespace_name from dba_ind_subpartitions          union all
 15  select def_tablespace_name from dba_part_lobs              union all
 16  select tablespace_name from dba_lob_partitions             union all
 17  select tablespace_name from dba_lob_subpartitions          union all
 18  select tablespace_name from dba_subpartition_templates     union all
 19  select tablespace_name from dba_lob_templates              union all
 20  select tablespace_name from dba_segments                   union all
 21  select tablespace_name from dba_extents                    union all
 22  select tablespace_name from dba_undo_extents
 23  )
 24  select tablespace_name from all_possible_ts
 25  minus
 26  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
--------------------
DEMO

Counting Rows

Here’s another little utility I use from time to time (usually for small tables) to check how many rows there are in each block of the table, and which blocks are used. It doesn’t do anything clever, just call routines in the dbms_rowid package for each rowid in the table:


rem
rem     Rowid_count.sql
rem     Generic code to count rows per block in a table
rem     Ordered by file and block
rem

define m_table = '&1'

spool rowid_count

select 
        dbms_rowid.rowid_relative_fno(rowid)    rel_file_no, 
        dbms_rowid.rowid_block_number(rowid)    block_no,
        count(*)                                rows_starting_in_block
from 
        &m_table        t1
group by 
        dbms_rowid.rowid_relative_fno(rowid), 
        dbms_rowid.rowid_block_number(rowid) 
order by 
        dbms_rowid.rowid_relative_fno(rowid), 
        dbms_rowid.rowid_block_number(rowid)
;


select
        rows_starting_in_block,
        count(*)        blocks
from
        (
        select 
                dbms_rowid.rowid_relative_fno(rowid), 
                dbms_rowid.rowid_block_number(rowid),
                count(*)                                rows_starting_in_block
        from 
                &m_table        t1
        group by 
                dbms_rowid.rowid_relative_fno(rowid), 
                dbms_rowid.rowid_block_number(rowid) 
        )
group by
        rows_starting_in_block
order by
        rows_starting_in_block
;

spool off


And here’s a sample of the output:


REL_FILE_NO   BLOCK_NO ROWS_STARTING_IN_BLOCK
----------- ---------- ----------------------
	 22	   131			  199
	 22	   132			  199
	 22	   133			  199
	 22	   134			  199
	 22	   135			   88
	 22	   138			  111

6 rows selected.


ROWS_STARTING_IN_BLOCK	   BLOCKS
---------------------- ----------
		    88		1
		   111		1
		   199		4

3 rows selected.


Obviously it could take quite a lot of I/O and CPU to run the two queries against a large table – generally I use it when I want to pick a block to dump afterwards.

Enhanced “validate” commands in Oracle’s Data Guard Broker 18c

If you are using an Oracle Database Enterprise Edition chances are that there is at least one environment in your estate making use of Data Guard. And if you are using Data Guard, why not use the broker? I have been using Data Guard broker for a long time now, and it has definitely improved a lot over the first releases, back in the day. I like it so much these days that I feel hard done by if I can’t make use of it. This is of course a matter of personal preference, and I might be exaggerating a little :)

One of the nice additions to the broker in Oracle 12.1 was the ability to validate a database before a role change. This is documented in the Data Guard broker documentation. I certainly don’t solely rely on the output of the command, I have my own checks I’m running that go over and above what a validate can do.

During research I ran the command on my 12.2 system and noticed much enhanced output. This is super exciting and worth blogging about. Well, that is, to me at least-I hope you find this post useful. I love discovering little changes like this, they aren’t always advertised on slide 1 in “What’s new in release X” presentations, but nevertheless great boosts to productivity.

This post was nearly complete, but then rested in my drafts folder for just a little too long and 18c has been released. Time for an update!

The environment

Before moving on, here’s the stack in case you find this via a search engine:

  • Oracle Linux 7.4 powering 2 VMs: server1 and server2
  • Oracle 18.3.0, single instance, no Oracle Restart
  • Data Guard Broker configuration managing 2 databases: NCDBA and NCDBB

The broker is quite happy with my setup, at least for now.

DGMGRL> show configuration

Configuration - test

  Protection Mode: MaxAvailability
  Members:
  NCDBA - Primary database
    NCDBB - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL> 

This is my setup, YMMV as always. Refer to your standard documents or other relevant documentation for more details about your configuration

New things to validate in 18c

With 12.2 it was possible to validate a datafile in addition to validating the database. Oracle 18c enhances the validate command quite a bit more:

DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] ;

  VALIDATE DATABASE [VERBOSE]  DATAFILE  
    OUTPUT=;

  VALIDATE DATABASE [VERBOSE]  SPFILE;

  VALIDATE FAR_SYNC [VERBOSE]  
    [WHEN PRIMARY IS ];

  VALIDATE NETWORK CONFIGURATION FOR { ALL |  };

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL |  };

DGMGRL> 

In this post I am going to focus on the verbose output generated by validate database, if I can find the time I’ll write about the other new options as well.

Validate database in Oracle 18c

Let’s have a look at the output of validate database verbose … Looking at the configuation status, NCDBB is currently running in recovery mode with NCDBA acting as the primary database. It makes sense to start with the validation of NCDBB first.

The output of the command is rather comprehensive as you will see, I have decided to annotate the output so you don’t have to scroll up and down that much.

DGMGRL> validate database verbose 'NCDBB'

  Database Role:     Physical standby database
  Primary Database:  NCDBA

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    NCDBA :  Off
    NCDBB :  Off

  Capacity Information:
    Database  Instances        Threads
    NCDBA      1               1
    NCDBB      1               1

  Managed by Clusterware:
    NCDBA :  NO
    NCDBB:  NO
    Validating static connect identifier for database NCDBA...
    The static connect identifier allows for a connection to database "NCDBA".

The first part of the output is related to the database’s role and status. Oracle reckons my standby database is ready for a role change (which I’ll double-check using my own tools and scripts). You can see that flashback database is not enabled (for reasons that don’t matter for this post).

Since both members are single instance databases it makes sense for them to have a single redo thread.

Another important piece of information can be found in the “managed by Clusterware” section. In releases prior to 12.1.0.2 you always had to statically register your databases with the listener for use with the broker. This has changed in 12.1.0.2: another one of these productivity boosters :) In modern releases you don’t need to statically register your databases with the listener provided Clusterware manages them. See MOS DocID 1387859.1 for all the details.

You read in the introduction that I’m using single instance Oracle databases without any Grid Infrastructure at all so this shiny new feature does not apply. There’s something else that might help though: Oracle validates the static connection identifier for you. It doesn’t simply ping the tns alias, the broker actually establishes a connection to the database. I noticed this in the listener.log: a program named dgmgrl connects to the database using the static conncetion identifier (as per “show database memberName staticconnectidentifier”). Output is formatted for readability:

2018-08-14 10:54:16.377000 +01:00
14-AUG-2018 10:54:16 * (CONNECT_DATA=(SERVICE_NAME=NCDBB_DGMGRL)(INSTANCE_NAME=NCDBB)(SERVER=DEDICATED)
(STATIC_SERVICE=TRUE)(CID=(PROGRAM=dgmgrl)(HOST=server1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)
(HOST=192.168.100.21)(PORT=27049)) * establish * NCDBB_DGMGRL * 0

Let’s continue with the output of the validate database command:

  Temporary Tablespace File Information:
    NCDBA TEMP Files:   1
    NCDBB TEMP Files:   1

  Data file Online Move in Progress:
    NCDBA:  No
    NCDBB:  No

This little section compares the number of temp files and warns you of any online data file move operations.

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds (computed 0 seconds ago)
    Transport Status:  Success


  Log Files Cleared:
    NCDBA Standby Redo Log Files:  Cleared
    NCDBB Online Redo Log Files:   Not Cleared
    NCDBB Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (NCDBA)                 (NCDBB)
    1         2                       3                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (NCDBB)                 (NCDBA)
    1         2                       3                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (NCDBA)                    (NCDBB)
    1          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (NCDBB)                   (NCDBA)
    1          200 MBytes                200 MBytes

This section is quite interesting as well as it allows you to have a gander at the transport lag and apply lag respectively. I can also see that I have standby redo logs both for my primary as well as the standby database. Sometimes the broker doesn’t get the number of standby redo logs right in “future log file groups configuration”. I found this to rectify itself after a switchover and switch-back.
Another reassuring fact is presented in the current and future configuration log file sizes: I follwed th documentation carefully and created all my (online and standby) redo logs the exact same size.

  Apply-Related Property Settings:
    Property                        NCDBA Value              NCDBB Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        NCDBA Value              NCDBB Value
    LogXptMode                      sync                     sync
    Dependency                                        
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    MaxConnections                  1                        1
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE
    LogShipping                     ON                       ON

The section just above compares apply and transport related settings between the primary and standby databases. These are stored in Data Guard broker properties eventually mapping back to database initialisation parameters.

  Automatic Diagnostic Repository Errors:
    Error                       NCDBA    NCDBB
    No logging operation        NO       NO
    Control file corruptions    NO       NO
    SRL Group Unavailable       NO       NO
    System data file missing    NO       NO
    System data file corrupted  NO       NO
    System data file offline    NO       NO
    User data file missing      NO       NO
    User data file corrupted    NO       NO
    User data file offline      NO       NO
    Block Corruptions found     NO       NO

DGMGRL> 

And finally, it appears as if the Automatic Diagnostic Repository (ADR) didn’t show any issues. This doesn’t mean there aren’t, maybe there is still something looming in your configuration that hasn’t been detected yet.

Summary

The validate database command is really quite useful in my opinion performing basic checks before role changes. It doesn’t take the responsibility off you for ensuring the role change will be a success but I found it to be a useful first indicator.

Oracle Adaptive Plan info in OTHER_XML

DBMS_XPLAN displays the operation ID with no gap, even for Adaptive Plans where the inactive operations are skipped. Did you ever wonder where the information of skipped rows is stored?

Here is a simple query (but please, remember that natural join is bad ;)

SQL> set feedback on sql_id
SQL> select * from dept natural join emp natural join bonus;
no rows selected
SQL_ID: 3q7fbwk91v4ra

The execution plan shows that the plan is adaptive:

SQL> select * from dbms_xplan.display_cursor(format=>'BASIC +note');
PLAN_TABLE_OUTPUT
------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from dept natural join emp natural join bonus
Plan hash value: 1315453310
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | BONUS |
| 5 | TABLE ACCESS FULL | EMP |
| 6 | INDEX UNIQUE SCAN | PK_DEPT |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPT |
------------------------------------------------
Note
-----
- this is an adaptive plan

With the ‘+adaptive format’ we see the active and inactive branches:

SQL> select * from dbms_xplan.display_cursor( sql_id=>'3q7fbwk91v4ra', format=>'BASIC +adaptive +note');
PLAN_TABLE_OUTPUT
------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from dept natural join emp natural join bonus
Plan hash value: 1315453310
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
|- 1 | HASH JOIN | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
|- 4 | STATISTICS COLLECTOR | |
| 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL | BONUS |
| 7 | TABLE ACCESS FULL | EMP |
| 8 | INDEX UNIQUE SCAN | PK_DEPT |
| 9 | TABLE ACCESS BY INDEX ROWID| DEPT |
|- 10 | TABLE ACCESS FULL | DEPT |
-------------------------------------------------
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

You can see that the numbering has changed: now 1–10 and this is what we actually have in PLAN_TABLE:

SQL> column "LPAD('',DEPTH,'')||OPERATION" format a30
SQL> select id,depth,parent_id,lpad(' ',depth,' ')||operation from v$sql_plan where sql_id like '3q7fbwk91v4ra';
ID      DEPTH  PARENT_ID LPAD('',DEPTH,'')||OPERATION
---------- ---------- ---------- ------------------------------
0 0 SELECT STATEMENT
1 1 0 HASH JOIN
2 2 1 NESTED LOOPS
3 3 2 NESTED LOOPS
4 4 3 STATISTICS COLLECTOR
5 5 4 HASH JOIN
6 6 5 TABLE ACCESS
7 6 5 TABLE ACCESS
8 4 3 INDEX
9 3 2 TABLE ACCESS
10 2 1 TABLE ACCESS

The ID here is unique. Adaptive Plan does not break this. So this means that it was mapped to different numbers when the inactive lines were skipped. DBMS_XPLAN does this for us but if we have our own tool we need to know where this mapping comes from.

/other_xml/display_map

As most of the additional information that we can see in the notes, they come from OTHER_XML

SQL> set long 100000 linesize 68
SQL> select other_xml from v$sql_plan where sql_id like '3q7fbwk91v4ra' and id=1;
OTHER_XML
--------------------------------------------------------------------
18.0.0.0176307
9200
13154533104080194477yesfo>><
![CDATA[DB_VERSION('18.1.0')]]>
nt>ATA[MERGE(@"SEL$58A6D7F6" >"SEL$3")]]>@"SEL$3")]]>
"SEL$2")]]>INE(@"SEL$2")]]>nt>
CDATA[FULL(@"SEL$9E43CB6E" "EMP"@"SEL$1")]]>DEX(@"SEL$9E43CB6E" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]>>T"@"SEL$1")]]>
SEL$1")]]>")]]>1")]]>
prt="0" dep="0" skp="1"/> op="4" dis="2" par="2" prt="0" dep="2" skp="1"/> par="2" prt="0" dep="3" skp="0"/> dis="7" par="1" prt="0" dep="2" skp="0"/>

Here is what we have in /other_xml/display_map:

SQL> select column_value from v$sql_plan , table(xmlsequence(extract(xmltype(other_xml),'/*/display_map/row'))) where sql_id like '3q7fbwk91v4ra' and id=1;
COLUMN_VALUE
--------------------------------------------------------------------
skp="1"/>


skp="1"/>





skp="1"/>

I can see one row per execution plan operation, referenced by ‘op’, the operation ID, the ‘par’, the PARENT_ID, and ‘dep’ the DEPTH. The ‘skp’ flags with 1 the inactive branches that must be skipped in the default format, and ‘dis’ is the display ID to map to in order to have no gap. I don’t think that the ‘par’ — PART_ID — is currently used by dbms_xplan.

When is this information translated from internal representation to XML? We can see some internal structure copy during Execution such as qesdpCopySharedToExeDisplayMap() and qesdpCopyExeToSharedDisplayMap() but the conversion to XML is done when querying V$SQL_PLAN.OTHER_XML:

#0  0x000000000916a390 in qesdpWriteDisplayMap ()
#1 0x00000000098c447d in qksxaCompactToCustomXml ()
#2 0x00000000098c489f in qksxaCompactToXml ()
#3 0x000000000befe794 in xplCompactToOtherXml ()
#4 0x000000000beff2ee in xplNodeToRow ()
#5 0x000000000bf0045d in xplMakeRow ()
#6 0x00000000121606aa in xplFetchRow ()
#7 0x000000000b0143e8 in kqlfgx ()
#8 0x0000000010d87767 in kglic_cbk ()
#9 0x0000000010d87035 in kglic0 ()
#10 0x0000000010d86d1c in kglic ()
#11 0x000000000b01095b in kqlfxp ()
#12 0x00000000040b1cf7 in qerfxFetch ()
#13 0x00000000120661af in opifch2 ()

It is interesting to see that even when this information could have been displayed as columns in V$SQL_PLAN, the CBO developers chose the ‘NoSQL’ way of putting everything new in XML. We see the limit when we try to build our own tools. I must admit that I have some awk scripts which parse the dbms_xplan.display_cursor() text output rather than trying to get information from V$SQL_PLAN…

About tools other than DBMS_XPLAN, SQL Monitor has the information in the XML. The Flash version differentiate active and inactive operations, but it looks like the SQLDev HTML5 does not (yet)grey the inactive one.

Dump logfile

Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.

The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available.


rem
rem     Script:         c_dump_log.sql
rem     Author:         Jonathan Lewis
rem     Dated:          December 2002
rem     Purpose:        Create procedured to dump the current online redo log file.
rem
rem     Last tested
rem             18.3.0.0
rem             12.2.0.1
rem             11.1.0.7
rem             11.2.0.6
rem             10.2.0.5
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Notes:
rem     Must be run as a DBA
rem     Very simple minded - no error trapping
rem     

create or replace procedure dump_log
as
        m_log_name      varchar2(255);
        m_process       varchar2(255);
        m_trace_name    varchar2(255);

begin
        select 
                lf.member
        into
                m_log_name
        from
                V$log           lo,
                v$logfile       lf
        where 
                lo.status = 'CURRENT'
        and     lf.group# = lo.group#
        and     rownum = 1
        ;

        execute immediate
        'alter system dump logfile ''' || m_log_name || '''';

        select
                spid
        into
                m_process
        from
                v$session       se,
                v$process       pr
        where
                se.sid = --dbms_support.mysid
                        (select sid from v$mystat where rownum = 1)
        and     pr.addr = se.paddr
        ;

        select
                tracefile
        into
                m_trace_name
        from
                v$session       se,
                v$process       pr
        where
                se.sid = --dbms_support.mysid
                        (select sid from v$mystat where rownum = 1)
        and     pr.addr = se.paddr
        ;

        dbms_output.put_line('Trace file is: ' || m_trace_name);
        dbms_output.put_line('Log file name is: ' || m_log_name);
        dbms_output.put_line('Trace file name includes: ' || m_process);


end;
/

show errors

drop public synonym dump_log;
create public synonym dump_log for dump_log;
grant execute on dump_log to public;

I don’t use the package often but if I want to find out what redo is generated during a test I usually follow the sequence:

  • alter system switch logfile;
  • do the experiment
  • execute dump_log

If you’re running in a PDB there’s an extra step needed as you can’t “switch logfile” inside a PDB so I’ll either do a log file switch before I start the test or (if there are steps in the test script that could generate a lot of log file I don’t want to see) I include a “pause” in the test script and use another session to do the logfile switch – in both cases the second session has to be connected to the CDB.

You will have noticed the creation of the public synonym and granting of the execute privilege to public. In my own sandbox database that’s a convenience – you may want to be a little more protective in your development and test systems.

The “dump logfile” command has a number of options for selective dumping – I have a note in my file commenting on these options, but I haven’t checked if there are any new ones (or changes to existing ones) for a long time:


alter system dump logfile '{filename}'
        scn min {first SCN to dump}
        scn max {last SCN to dump}
        time min {seconds since midnight at the end of 1st Sept 1987}
        time max {see redo_time_calc.sql}
        layer {integer} opcode {integer} e.g.:
                layer 23        Block Written Records
                layer 5         Undo handling in general
                layer 5 opcode 4        Undo Seg header on commit; or rollback;
                layer 9999 opcode 9999  Trick to validate the whole log file structure
        xid {usn} {slot} {sequence}     -- 10g only, may break on IMU redo (see below)
        objno {object_id}               -- 10g only, may break on IMU redo (see below)
        dba min {datafile no} . {blockno} -- with spaces either side of the dot.
        dba max {datafile no} . {blockno} -- with spaces either side of the dot.
        rba min {log file seq no} . {blockno} -- with spaces either side of the dot.
        rba max {log file seq no} . {blockno} -- with spaces either side of the dot..
(The dots in the last four options becomes invalid syntax in 10g).

The introduction to this note references back to a presentation I did in the year 2000, but the closing comment suggests that I probably haven’t checked the list since some time in the 10g timeline.

The reference to redo_time_calc.sql points to the following script, that expresses the time as the number of seconds since Jan 1988, with the unfortunate simplification that Oracle thinks there are 31 days in every month of the year:


rem
rem     Script:         redo_time_calc3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2012
rem     Purpose:
rem

select 
        86400 * (
                31 *
                        months_between(
                                trunc(sysdate,'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        ) +
                sysdate - trunc(sysdate,'MM')
        )       redo_now
from 
        dual
;



select 
        86400 * (
                (sysdate - 10/1440) - trunc((sysdate-10/1440),'MM') + 
                31 * 
                        months_between(
                                trunc((sysdate - 10/1440),'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        )
                )               ten_minutes_ago,
        86400 * (
                sysdate - trunc(sysdate,'MM') + 
                31 * 
                        months_between(
                                trunc(sysdate,'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        )
                )               redo_now,
        to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')        now
from 
        dual
;

This isn’t a piece of code I use much – the original version (which I published in Oracle Core, p.241) was something I wrote in 2003 and had to adjust by hand each time I used it without realising that I’d got it wrong. Luckily someone pointed out my error and gave me the corrected code a little while after I’d published the book. (It was one of those “why didn’t I think of that” moments – it seemed so obvious after he’d told me the right answer.)

Log in to Ubuntu VMs in Oracle Cloud Infrastructure

When I learned that Oracle was providing Ubuntu images in Oracle Cloud Infrastructure (OCI) I was a bit surprised at first. After all, Oracle provides a great Enterprise Linux distribution in the form of Oracle Linux. As a Ubuntu fan I do of course appreciate the addition of Ubuntu to the list of supported distributions. In fact it doesn’t end there, have a look at the complete list of Oracle provided images to see what’s available.

Trying Ubuntu LTS

I wanted to give Ubuntu a spin on OCI and decided to start a small VM using the 16.04 LTS image. I have been using this release quite heavily in the past and have yet to make the transition to 18.04. Starting the 16.04 VM up was easily done using my terraform script. Immediately after the terraform prompt returned I faced a slight issue: I couldn’t log in:

$ ssh opc@w.x.y.z
The authenticity of host ... can't be established.
...
opc@w.x.y.z: Permission denied (publickey)

This is entirely my fault, for some reason I didn’t scroll down within the page to read more about users. Assuming the account created during the VM provisioning would be the same as for the Oracle Linux image, I tried logging in as user “opc”. The result is what I showed you earlier in the listing.

The clue about users is found in Linux Image Details, section “users” and aforementioned documentation page. I am quoting verbally because I couldn’t possibly say it any better:

For instances created using the Ubuntu image, the user name ubuntu is created automatically. The ubuntu user has sudo privileges and is configured for remote access over the SSH v2 protocol using RSA keys. The SSH public keys that you specify while creating instances are added to the /home/ubuntu/.ssh/authorized_keys file.

There it is.

It seems I wasn’t the only one, and beginning with the Canonical-Ubuntu-16.04-2018.11.15-0 image, a message is displayed when you try to log in as opc:

$ ssh opc@w.x.y.z
...
Warning: Permanently added ... to the list of known hosts
Please login as the user "ubuntu" rather than the user "opc".

Connection to w.x.y.z closed
$ 

So no more missing this important piece of information :)

Shrink Space

I have never been keen on the option to “shrink space” for a table because of the negative impact it can have on performance.

I don’t seem to have written about it in the blog but I think there’s something in one of my books pointing out that the command moves data from the “end” of the table (high extent ids) to the “start” of the table (low extent ids) by scanning the table backwards to find data that can be moved and scanning forwards to find space to put it. This strategy can have the effect of increasing the scattering of the data that you’re interested in querying if most of your queries are about “recent” data, and you have a pattern of slowing deleting aging data. (You may end up doing a range scan through a couple of hundred table blocks for data at the start of the table that was once packed into a few blocks near the end of the table.)

In a discussion with a member of the audience at the recent DOAG conference (we were talking about execution plans for queries that included filter subqueries) I suddenly thought of another reason why (for an unlucky person) the shrink space command could be a disaster – here’s a little fragment of code and output to demonstrate the point.


rem
rem     Script:         shrink_scalar_subq.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2018
rem     Purpose:
rem
rem     Versions tested
rem             12.2.0.1
rem

select
        /*+ gather_plan_statistics pre-shrink */
        count(*)
from    (
        select  /*+ no_merge */
                outer.*
        from
                emp outer
        where
                outer.sal > (
                        select  /*+ no_unnest */
                                avg(inner.sal)
                        from
                                emp inner
                        where
                                inner.dept_no = outer.dept_no
                )
        )
;

alter table emp enable row movement;
alter table emp shrink space compact;

select
        /*+ gather_plan_statistics post-shrink  */
        count(*)
from    (
        select  /*+ no_merge */
                outer.*
        from emp outer
        where outer.sal >
                (
                        select /*+ no_unnest */ avg(inner.sal)
                        from emp inner
                        where inner.dept_no = outer.dept_no
                )
        )
;

The two queries are the same and the execution plans are the same (the shrink command doesn’t change the object statistics, after all), but the execution time jumped from 0.05 seconds to 9.43 seconds – and the difference in timing wasn’t about delayed block cleanout or other exotic side effects.


  COUNT(*)
----------
      9498

Elapsed: 00:00:00.05


  COUNT(*)
----------
      9498

Elapsed: 00:00:09.43

The query is engineered to have a problem, of course, and enabling rowsource execution statistics exaggerates the anomaly – but the threat is genuine. You may have seen my posting (now 12 years old) about the effects of scalar subquery caching – this is another example of the wrong item of data appearing in the wrong place making us lose the caching benefit. The emp table I’ve used here is (nearly) the same emp table I used in the 2006 posting, but the difference between this case and the previous case is that I updated a carefully selected row to an unlucky value in 2006, but here in 2018 the side effects of a call to shrink space moved a row from the end of the table (where it was doing no harm) to the start of the table (where it had a disastrous impact).

Here are the two execution plans – before and after the shrink space – showing the rowsource execution stats. Note particularly the number of times the filter subquery ran – jumping from 7 to 3172 – the impact this has on the buffer gets, and the change in time recorded:

----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:00.03 |    1880 |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:00.03 |    1880 |
|   2 |   VIEW                |      |      1 |    136 |   9498 |00:00:00.03 |    1880 |
|*  3 |    FILTER             |      |      1 |        |   9498 |00:00:00.03 |    1880 |
|   4 |     TABLE ACCESS FULL | EMP  |      1 |  19001 |  19001 |00:00:00.01 |     235 |
|   5 |     SORT AGGREGATE    |      |      7 |      1 |      7 |00:00:00.02 |    1645 |
|*  6 |      TABLE ACCESS FULL| EMP  |      7 |   2714 |  19001 |00:00:00.02 |    1645 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("OUTER"."SAL">)
   6 - filter("INNER"."DEPT_NO"=:B1)


----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:09.42 |     745K|
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:09.42 |     745K|
|   2 |   VIEW                |      |      1 |    136 |   9498 |00:00:11.71 |     745K|
|*  3 |    FILTER             |      |      1 |        |   9498 |00:00:11.70 |     745K|
|   4 |     TABLE ACCESS FULL | EMP  |      1 |  19001 |  19001 |00:00:00.01 |     235 |
|   5 |     SORT AGGREGATE    |      |   3172 |      1 |   3172 |00:00:09.40 |     745K|
|*  6 |      TABLE ACCESS FULL| EMP  |   3172 |   2714 |     10M|00:00:04.33 |     745K|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("OUTER"."SAL">)
   6 - filter("INNER"."DEPT_NO"=:B1)


Footnote:

For completeness, here’s the code to generate the emp table. It’s sitting in a tablespace using system managed extents and automatic segment space management.


create table emp(
        dept_no         not null,
        sal,
        emp_no          not null,
        padding,
        constraint e_pk primary key(emp_no)
)
as
with generator as (
        select  null
        from    dual
        connect by
                level <= 1e4 -- > comment to avoid wordpress format issue
)
select
        mod(rownum,6),
        rownum,
        rownum,
        rpad('x',60)
from
        generator       v1,
        generator       v2
where
        rownum <= 2e4 -- > comment to avoid wordpress format issue
;


insert into emp values(432, 20001, 20001, rpad('x',60));
delete /*+ full(emp) */ from emp where emp_no <= 1000;      -- > comment to avoid wordpress format issue
commit;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          => 'EMP',
                method_opt       => 'for all columns size 1'
        );
end;
/



 

Data Warehouse Design: To Index, or Not to Index, that is the question

This post is part of a series that discusses some common issues in data warehouses.

When you query a star schema, you essentially have two choices;

  • bitmap index and star transformation 
  • full scan, bloom filter, and hash join

Star Transformation 

Star transformation was introduced in Oracle 8(see also Oracle Optimizer Blog: Optimizer Transformations: Star Transformation).   A star transformation requires:

  • A bitmap index on each foreign key column(s) on the FACT table
    • Though you don't actually need the foreign key to be defined
    • An index on primary key on the DIMENSION table
  • Set STAR_TRANSFORMATION_ENABLED=TRUE to enable the transformation.
    • Or use the /*+STAR_TRANSFORMATION*/ hint.

Each dimension is queried, and the results iterated through the bitmap index on the corresponding foreign key. The results for each bitmap are merged with a bitmap AND operation, and the result is converted back to rowids on the fact table.

If you were going to try to do this with conventional b-tree indexes, you would need an index for each permutation of foreign key combinations.
For example, this query reports US sales in 1999 by customer state:

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT  c.country_name
, u.cust_state_province
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
from sales s
, customers u
, products p
, times t
, countries c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND u.country_id = c.country_id
AND c.country_iso_code = 'US'
AND t.fiscal_year = 1999
GROUP BY c.country_name
, u.cust_state_province
ORDER BY 1,2
/

The execution plan below shows:

  • Line 2: A temporary table that is the combination of countries and customers is created.
  • Line 18: Bitmap look-up of  TIMES dimensions on the SALES_TIME_BIX bitmap index.
  • Line 23: Bitmap look-up of the temporary table against the SALES_CUST_BIX bitmap index
  • Line 13: A bitmap AND is performed of the two bitmap results
  • Line 8: The temporary table is used again to join in the customer and state description.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 3988189767

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2133 (100)| | | | 45 |00:00:02.74 | 98078 | 195 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 45 |00:00:02.74 | 98078 | 195 | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D77CE_A4BC21 | 1 | | | | | | | 0 |00:00:00.08 | 1525 | 0 | 1024 | 1024 | |
| 3 | HASH JOIN | | 1 | 2921 | 111K| 418 (1)| 00:00:01 | | | 18520 |00:00:00.05 | 1524 | 0 | 1185K| 1185K| 599K (0)|
| 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | 0 | | | |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 1138K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | 0 | | | |
| 6 | SORT GROUP BY | | 1 | 2359 | 101K| 1715 (1)| 00:00:01 | | | 45 |00:00:02.66 | 96552 | 195 | 6144 | 6144 | 6144 (0)|
| 7 | HASH JOIN | | 1 | 12057 | 518K| 1713 (1)| 00:00:01 | | | 141K|00:00:02.49 | 96552 | 195 | 2391K| 1595K| 2015K (0)|
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D77CE_A4BC21 | 1 | 2921 | 75946 | 6 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | 0 | | | |
| 9 | VIEW | VW_ST_B6F3B15C | 1 | 12057 | 211K| 1707 (1)| 00:00:01 | | | 141K|00:00:02.18 | 96552 | 195 | | | |
| 10 | NESTED LOOPS | | 1 | 12057 | 553K| 1684 (1)| 00:00:01 | | | 141K|00:00:02.08 | 96552 | 195 | | | |
| 11 | PARTITION RANGE SUBQUERY | | 1 | 12056 | 294K| 322 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| 141K|00:00:01.05 | 96102 | 0 | | | |
| 12 | BITMAP CONVERSION TO ROWIDS | | 5 | 12056 | 294K| 322 (1)| 00:00:01 | | | 141K|00:00:00.94 | 96047 | 0 | | | |
| 13 | BITMAP AND | | 5 | | | | | | | 5 |00:00:00.87 | 96047 | 0 | | | |
| 14 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.02 | 1914 | 0 | 1024K| 512K|40960 (0)|
| 15 | BITMAP KEY ITERATION | | 5 | | | | | | | 364 |00:00:00.02 | 1914 | 0 | | | |
| 16 | BUFFER SORT | | 5 | | | | | | | 1820 |00:00:00.01 | 55 | 0 | 73728 | 73728 | |
| 17 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
| 18 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | 1820 | | | | |KEY(SQ)|KEY(SQ)| 364 |00:00:00.01 | 1859 | 0 | | | |
| 19 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.84 | 94133 | 0 | 8624K| 1078K| 319K (0)|
| 20 | BITMAP KEY ITERATION | | 5 | | | | | | | 6566 |00:00:00.81 | 94133 | 0 | | | |
| 21 | BUFFER SORT | | 5 | | | | | | | 92600 |00:00:00.14 | 0 | 0 | 28M| 1930K| 928K (0)|
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D77CE_A4BC21 | 1 | 2921 | 14605 | 6 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | 0 | | | |
| 23 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | 92600 | | | | |KEY(SQ)|KEY(SQ)| 6566 |00:00:00.54 | 94133 | 0 | | | |

| 24 | TABLE ACCESS BY USER ROWID | SALES | 141K| 1 | 22 | 1385 (1)| 00:00:01 | ROWID | ROWID | 141K|00:00:00.76 | 450 | 195 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- cbqt star transformation used for this statement
- rely constraint used for this statement

The temporary table optimisation seen above for the join back to the dimension table was introduced in 10g. It can be suppressed by setting:

  • STAR_TRANSFORMATION_ENABLED=TEMP_DISABLE 

Without this optimisation, Oracle needs to visit both customers and countries table a second time, whereas before we only visited the temporary table twice.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2782741738

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2950 (100)| | | | 45 |00:00:02.91 | 99599 | | | |
| 1 | SORT GROUP BY | | 1 | 45 | 2565 | 2950 (1)| 00:00:01 | | | 45 |00:00:02.91 | 99599 | 6144 | 6144 | 6144 (0)|
|* 2 | HASH JOIN | | 1 | 635 | 36195 | 2949 (1)| 00:00:01 | | | 141K|00:00:02.77 | 99599 | 12M| 2994K| 16M (0)|
| 3 | MERGE JOIN CARTESIAN | | 1 | 12057 | 423K| 2533 (1)| 00:00:01 | | | 141K|00:00:02.39 | 98078 | | | |
|* 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | |
| 5 | BUFFER SORT | | 1 | 12057 | 211K| 2531 (1)| 00:00:01 | | | 141K|00:00:02.29 | 98076 | 6219K| 1010K| 5527K (0)|
| 6 | VIEW | VW_ST_958ED0D5 | 1 | 12057 | 211K| 2531 (1)| 00:00:01 | | | 141K|00:00:02.03 | 98076 | | | |
| 7 | NESTED LOOPS | | 1 | 12057 | 553K| 2097 (1)| 00:00:01 | | | 141K|00:00:01.92 | 98076 | | | |
| 8 | PARTITION RANGE SUBQUERY | | 1 | 12056 | 294K| 734 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| 141K|00:00:01.16 | 97626 | | | |
| 9 | BITMAP CONVERSION TO ROWIDS| | 5 | 12056 | 294K| 734 (1)| 00:00:01 | | | 141K|00:00:01.05 | 97571 | | | |
| 10 | BITMAP AND | | 5 | | | | | | | 5 |00:00:00.98 | 97571 | | | |
| 11 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.02 | 1914 | 1024K| 512K|40960 (0)|
| 12 | BITMAP KEY ITERATION | | 5 | | | | | | | 364 |00:00:00.02 | 1914 | | | |
| 13 | BUFFER SORT | | 5 | | | | | | | 1820 |00:00:00.01 | 55 | 73728 | 73728 | |
|* 14 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | | | |
|* 15 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | 1820 | | | | |KEY(SQ)|KEY(SQ)| 364 |00:00:00.01 | 1859 | | | |
| 16 | BITMAP MERGE | | 5 | | | | | | | 5 |00:00:00.96 | 95657 | 8624K| 1078K| 319K (0)|
| 17 | BITMAP KEY ITERATION | | 5 | | | | | | | 6566 |00:00:00.92 | 95657 | | | |
| 18 | BUFFER SORT | | 5 | | | | | | | 92600 |00:00:00.21 | 1524 | 36M| 2148K| 1180K (0)|
|* 19 | HASH JOIN | | 1 | 2921 | 52578 | 418 (1)| 00:00:01 | | | 18520 |00:00:00.04 | 1524 | 1922K| 1922K| 565K (0)|
|* 20 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 8 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | |
| 21 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | | | |
|* 22 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | 92600 | | | | |KEY(SQ)|KEY(SQ)| 6566 |00:00:00.58 | 94133 | | | |
| 23 | TABLE ACCESS BY USER ROWID | SALES | 141K| 1 | 22 | 1797 (1)| 00:00:01 | ROWID | ROWID | 141K|00:00:00.48 | 450 | | | |
| 24 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 1138K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.03 | 1521 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Without Star Transformation: Full Scan-Bloom Filter 

A Bloom filter is a form of bitmap. Each dimension value is hashed and maps to a bit in the map. The fact table is pushed through the same hash, and if the bit in the map was set by hashing the dimensions, then it is a possible match.  It is only a possible match because multiple values might hash to the same value. So, you can get false positives that Oracle then filters out by exactly joining them with a hash-join.  However, this hash is much smaller than if the Bloom filter had not been used.
Bloom Filters were originally introduced into Oracle in 10gR2, but there was no documentation. See also:

Bloom Filters are frequently more effective for larger results set even on conventional systems

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2588754131

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1586 (100)| | | | 45 |00:00:00.46 | 2063 | 472 | | | |
| 1 | SORT GROUP BY | | 1 | 45 | 3105 | 1586 (3)| 00:00:01 | | | 45 |00:00:00.46 | 2063 | 472 | 6144 | 6144 | 6144 (0)|
|* 2 | HASH JOIN | | 1 | 12057 | 812K| 1585 (3)| 00:00:01 | | | 141K|00:00:00.31 | 2063 | 472 | 1995K| 1995K| 1655K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 4 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 48360 | 2691K| 1568 (3)| 00:00:01 | | | 169K|00:00:00.27 | 2007 | 472 | 2391K| 1595K| 1990K (0)|
|* 6 | HASH JOIN | | 1 | 2921 | 111K| 418 (1)| 00:00:01 | | | 18520 |00:00:00.03 | 1524 | 0 | 1236K| 1236K| 728K (0)|
|* 7 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | 0 | | | |
| 8 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 1138K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | 0 | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 15M| 1142 (3)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.15 | 482 | 472 | | | |
| 10 | TABLE ACCESS FULL | SALES | 5 | 918K| 15M| 1142 (3)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.15 | 482 | 472 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Which is better? 

It depends. Often Bloom filter performance is better, but on queries that return very few values, you might find the star transformation outperforms the Bloom filter.  The Oracle documentation says that star transformation is a cost-based optimisation.  It is only used if the plan is cheaper.  Although I have found some cases where it star transforms to a more expensive plan.

In the next test, I will query the software sales for 1999 by region in a named country, from the largest (USA) to the smallest (New Zealand). I will test with and without STAR_TRANSFORMATION_ENABLED and record the actual runtime and number of buffers for the execution plan.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">select  c.country_name
, u.cust_state_province
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
from sales s
, customers u
, products p
, times t
, countries c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND u.country_id = c.country_id
AND c.country_iso_code = '&&iso_country_code'
AND p.prod_category_id = 205
and t.fiscal_year = 1999
GROUP BY c.country_name
, u.cust_state_province
ORDER BY 1,2
/

The data by country is highly skewed. That will let me compare star transformation -v- Bloom filter for different data volumes. As the number of sales decreases the elapsed time of the star transformation falls below that of the Bloom filter. The faster query is shown in bold.

#4472C4; border-right: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
ISO Country Code
#4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Country Name
#4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
Number of Sales in 1999
#4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
Number of Sales
#4472C4; border-bottom: solid white 1.0pt; border-left: none; border-right: none; border-top: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-bottom-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.66%;" valign="top" width="20%">
Star Transformation
#4472C4; border-left: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-right-alt: solid white .5pt; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 22.08%;" valign="top" width="22%">
Full Scan-Bloom Filter
#B4C6E7; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
A-Time
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
Buffers
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
A-Time
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
Buffers
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
US
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
United States of America
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
2662
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
526212
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
1.76
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
97999
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.55
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
DE
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Germany
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
561
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
81978
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.51
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
45572
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.29
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
JP
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Japan
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
281
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
60183
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.16
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
7118
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.23
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
GB
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
United Kingdom
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
391
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
58638
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.38
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
42339
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.25
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
IT
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Italy
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
257
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
42570
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.53
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
43526
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.38
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
AU
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Australia
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
228
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
33685
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.14
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
8113
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.26
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
FR
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
France
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
161
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
33078
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.3
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
23401
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.27
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
SG
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Singapore
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
80
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
25253
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.16
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
6928
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.4
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
CA
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Canada
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
90
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
22858
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.27
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
14118
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.32
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
ES
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Spain
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
85
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
17136
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.16
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
14282
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.22
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
DK
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Denmark
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
89
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
16651
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.08
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
5844
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.24
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
AR
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Argentina
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
3
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
202
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.07
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
5724
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.21
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
BR
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Brazil
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
9
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
180
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.09
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
7912
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.26
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
TR
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Turkey
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
1
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
168
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.05
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
4127
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.19
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
CN
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
China
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
4
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
19
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.1
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
7265
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.18
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 1.0pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
PL
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Poland
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
2
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
18
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.11
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
7267
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.23
#B4C6E7; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 102; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068
#4472C4; border-top: none; border: solid white 1.0pt; height: 8.5pt; mso-background-themecolor: accent1; mso-border-alt: solid white .5pt; mso-border-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.72%;" valign="top" width="11%">
SA
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 20.38%;" valign="top" width="20%">
Saudi Arabia
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 12.08%;" valign="top" width="12%">
0
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 13.06%;" valign="top" width="13%">
7
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 9.84%;" valign="top" width="9%">
0.07
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.82%;" valign="top" width="10%">
4037
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 10.5%;" valign="top" width="10%">
0.26
#D9E2F3; border-bottom: solid white 1.0pt; border-left: none; border-right: solid white 1.0pt; border-top: none; height: 8.5pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid white .5pt; mso-border-bottom-themecolor: background1; mso-border-left-alt: solid white .5pt; mso-border-left-themecolor: background1; mso-border-right-themecolor: background1; mso-border-themecolor: background1; mso-border-top-alt: solid white .5pt; mso-border-top-themecolor: background1; padding: 0cm 5.4pt 0cm 5.4pt; width: 11.58%;" valign="top" width="11%">
2068

The exact balance point between the two will depend on many factors, the point here is the trend.

The Trouble with Bitmap Indexes 

"There is a locking implication by design in bitmap indexes, the keys point to hundreds of rows and if I lock a single key entry in a bitmap index, I've locked the key entry for hundreds of other rows.
That is WHY bitmaps are not recommended - if you update a bitmap indexed column OR you insert/delete from the table (which will modify the bitmap index), you'll find serialization at best and deadlocks all over the place at worst" (Tom Kyte, 2007).
You may also see the size of the bitmap index grow significantly and quickly.
Jonathan Lewis has written extensively on the subject of Bitmap Indexes on his blog:

So bitmap indexes were really only ever an option where you bulk load the data, and then the data is static while you query it. You might even drop the index prior to loading data and then rebuild it again afterward. They are fast to build because there is no sort involved.

In choosing whether to still use bitmaps indexes instead of Bloom filters, you have to decide whether the improved performance for a probably small number of queries that return a small volume of data is worth the challenges associated with bitmap indexes.  In general, I think it probably isn't.

IRR function in PLSQL

We had an AskTOM question recently about how to calculate the IRR, ie, the Internal Rate of Return. To be honest, I had not really heard of the function, but readers were quick to point out to me that it was a commonly used function in the most “popular” database on earth, namely Microsoft Excel Smile

It turns out that it is an iterative function, ie, there is no equation that calculates it directly – you start with an opening guess and then iterate until each guess gets closer to the answer within an accepted tolerance. A little research took me on a nice trip down memory lane to my university (college) days of a maths major where we learned the Newton Raphson method (although apparently it is more commonly referred to as Newton’s method).

So a little PL/SQL later, I’ve re-embraced my youthful mathematical roots. If only we could iterate like this function back to our youth as well Smile

Here is some test data that we can use


SQL> create table test_tbl
  2  (
  3  id number,
  4  cash_flow number
  5  );

Table created.

SQL>
SQL> insert into test_tbl (id,cash_flow) values (0,-6000000);
SQL> insert into test_tbl (id,cash_flow) values (1,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (2,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (3,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (4,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (5,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (6,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (7,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (8,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (9,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (10,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (11,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (12,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (13,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (14,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (15,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (16,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (17,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (18,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (19,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (20,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (21,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (22,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (23,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (24,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (25,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (26,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (27,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (28,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (29,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (30,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (31,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (32,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (33,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (34,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (35,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (36,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (37,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (38,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (39,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (40,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (41,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (42,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (43,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (44,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (45,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (46,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (47,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (48,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (49,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (50,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (51,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (52,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (53,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (54,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (55,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (56,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (57,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (58,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (59,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (60,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (61,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (62,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (63,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (64,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (65,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (66,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (67,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (68,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (69,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (70,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (71,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (72,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (73,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (74,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (75,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (76,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (77,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (78,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (79,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (80,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (81,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (82,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (83,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (84,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (85,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (86,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (87,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (88,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (89,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (90,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (91,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (92,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (93,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (94,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (95,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (96,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (97,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (98,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (99,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (100,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (101,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (102,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (103,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (104,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (105,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (106,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (107,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (108,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (109,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (110,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (111,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (112,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (113,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (114,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (115,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (116,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (117,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (118,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (119,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (120,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (121,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (122,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (123,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (124,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (125,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (126,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (127,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (128,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (129,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (130,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (131,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (132,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (133,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (134,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (135,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (136,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (137,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (138,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (139,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (140,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (141,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (142,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (143,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (144,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (145,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (146,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (147,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (148,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (149,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (150,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (151,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (152,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (153,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (154,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (155,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (156,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (157,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (158,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (159,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (160,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (161,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (162,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (163,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (164,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (165,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (166,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (167,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (168,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (169,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (170,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (171,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (172,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (173,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (174,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (175,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (176,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (177,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (178,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (179,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (180,54802.38);

And here is the function using Newton’s method. You can see in line 15, that we iterate until we get within a nominated threshold. I could code the function just to read my test data above, but that does not make it very practical for an arbitrary set of data. So I’ve parameterised the function to take a CURSOR variable so that any resultset can be passed to it. As long as the data is pass in the correct order, the function should operate just fine.

Warning: Because this is a function that iterates until a threshold is reached, if you send in garbage data it is quite possible that the function will either never return, or crash with a numeric overflow error. If you are planning on using the code below, you may want to add some sanity checks to “harden” it for production usage.


SQL> create or replace
  2  function irr(rc sys_refcursor) return number is
  3    type nlist is table of number index by pls_integer;
  4    l_values nlist;
  5
  6    l_threshold number := 0.005;
  7    l_guess number := l_threshold + 1;
  8    l_next_guess number := 2;
  9    l_irr number := 1;
 10
 11  begin
 12    fetch rc bulk collect into l_values;
 13    close rc;
 14
 15    while abs(l_guess) > l_threshold
 16    loop
 17      l_guess := 0;
 18      l_next_guess := 0;
 19      for i in 1 .. l_values.count
 20      loop
 21        l_guess := l_guess + l_values(i)/power(1+l_irr/100, i-1);
 22        l_next_guess := l_next_guess + -i*l_values(i)/power(1+l_irr/100, i-1);
 23      end loop;
 24      l_irr := l_irr - l_guess/l_next_guess;
 25
 26      --dbms_output.put_line('l_irr='||l_irr);
 27      --dbms_output.put_line('l_guess='||l_guess);
 28      --dbms_output.put_line('l_next_guess='||l_next_guess);
 29    end loop;
 30    return l_irr;
 31  end;
 32  /

Function created.

And let’s give it a test.


SQL>
SQL> select irr(cursor(select cash_flow from test_tbl order by id asc )) irr
  2  from dual;

       IRR
----------
.490408759

1 row selected.

SQL>

Bait and Switch

Just what you need after a long hike in the Lake District:
https://jonathanlewis.files.wordpress.com/2018/11/baitnswitch1.jpg?w=113... 113w, https://jonathanlewis.files.wordpress.com/2018/11/baitnswitch1.jpg?w=225... 225w" sizes="(max-width: 603px) 100vw, 603px" />