Prepare POI for Sygic

Sygic doesn’t support POI and routes in standard KML format.
To load POI to Sygic from google mymaps you have to
1. Export layers to KML format (or KMZ format – its just archive with KML plus icons for places). KML is just XML format
2. Process KML file with XSLT transfomation

<!-- longitude | latitude | name | address | phone | fax | web | email | short description | long description -->
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:df="http://www.opengis.net/kml/2.2">
  <xsl:output method="text" encoding="utf-8" />

  <xsl:param name="delim" select="'|'" />
  <xsl:param name="quote" select="'&quot;'" />
  <xsl:param name="break" select="'&#xA;'" />

  <xsl:template match="/">
    <xsl:apply-templates select="df:kml/df:Document/df:Folder/df:Placemark" />
  </xsl:template>

  <xsl:template match="df:Placemark">
    <xsl:value-of select="substring-before(substring-after(normalize-space(df:Point/df:coordinates), ','), ',')"/>|<xsl:value-of select="substring-before(normalize-space(df:Point/df:coordinates), ',')"/>|<xsl:value-of select="normalize-space(df:name)"/>|||||||<xsl:value-of select="normalize-space(df:description)"/>
    <xsl:if test="following-sibling::*">
      <xsl:value-of select="$break" />
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

I did it with Notepad++ text editor with XML Tools plugin

Next steps was taken from Sygic site
3. Download CSV to RUPI converter
4. Process file from step 2 with converter
5. Copy created upi/rupi files to Sygic/maps/import folder (create import folder if you don’t have such)

Unfortunately Sygic support told me, that they have no plans to support KML/KMZ format like free MapsMe do.

How to read FILTER operation

Из книги Expert Oracle SQL: Optimization, Deployment, and Statistics

if you see a FILTER operation with more than one operand then the
second and subsequent operands (the subquery or subqueries) are
evaluated for each row returned by the first operand (the main query)

Merge and unnest

Опять из книги Expert Oracle SQL: Optimization, Deployment, and Statistics разница между MERGE и UNNEST

• View merging applies to inline views, factored subqueries, and data
dictionary views that appear as row sources in the FROM clause of an
enclosing query block. View merging is controlled by the MERGE and
NO_MERGE hints.

• Subquery unnesting relates to subqueries in the
SELECT list, WHERE clause, or anywhere else that Oracle may in the
future support. Subquery unnesting is controlled by the UNNEST and
NO_UNNEST hints

UNNEST – это про WHERE, SELECT и управляется при помощи UNNEST – NO_UNNEST hints

VIEW MERGING – это про FROM, управляется при помощи MERGE – NO_MERGE hints
Бывает 2 видов:
SIMPLE VIEW MERGING – heuristic transformation, т.е. применяется безусловно, но может быть отменена хинтами.
COMPLEX VIEW MERGING – cost based transformation, применяется, если subquery содержит DISTINCT или GROUP BY.

Nested loops в планах

NB:

In the case of NESTED LOOPS the estimated row count is per iteration
of the loop whereas the actual row count is for all iterations of the
loop.

Restoring objects statistics

From book Expert Oracle SQL: Optimization, Deployment, and Statistics I found new thing: you don’t need to export-import automatically gathered statistics. Instead of you can use DBMS_STATS.RESTORE* procedures.
By default oracle stores data for 31 days.
Quote from Chapter 9 of the book:

• There are several procedures for restoring statistics including DBMS_STATS.RESTORE_SCHEMA_STATS.
• User statistics set with DBMS_STATS.SET_xxx_STATS procedures are not restored. So, for example, any hand-crafted histogram would have to be reapplied after statistics are restored.
• Although this is normally the default behavior, it is good practice to explicitly invalidate any bad plans in the shared pool by using the NO_INVALIDATE => FALSE parameter setting
• The view DBA_OPTSTAT_OPERATIONS provides a history of gather and restore operations.
• By default superseded statistics are retained for 31 days. This can be managed by the function DBMS_STATS.GET_STATS_HISTORY_RETENTION and the procedure DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.