Hi All!
I am wondering what is the best and fastest approach to convert comma separated list into rows.
At the moment I am using:
select regexp_substr('1,2,3,4','[^,]+', 1, level)
from dual
connect by regexp_substr('1,2,3,4', '[^,]+', 1, level) is not null;
but this is very slow when the list is huge.
For example (the list's length is 3619):
select regexp_substr('1803,1811,1984,1965,2058,1942,1772,1776,2035,1808,2140,1804,1881,2086,2142,2021,1779,2039,2070,1929,1865,1798,2108,1957,1969,1995,1800,2117,2025,1809,1970,1952,1947,1931,2000,1806,1999,1821,1774,2066,1825,2101,2056,1948,2145,1930,1989,1949,1981,1791,2090,1950,2154,1944,2032,2048,2120,1987,1789,2136,2028,2089,2105,1787,2116,1956,1851,1829,1822,2054,1990,2041,1781,2096,1890,2073,2107,2094,2139,1799,2064,2052,2130,2132,2075,1907,1932,2023,2099,1927,2088,2068,2009,1918,2067,1961,2005,2006,2128,2119,2029,2134,2095,2092,2097,2040,1826,1866,1810,2082,1912,1891,2150,2135,2127,2049,1919,2146,2062,1778,1978,1959,1882,2111,2008,1955,1997,1904,1922,1848,2036,1964,1953,1962,1924,1994,1923,1968,2112,2001,2011,2144,1793,2010,1914,1858,1783,1971,2087,1992,1849,1805,2104,2131,1817,2037,1777,2030,2022,2077,1951,1928,1920,2044,2019,1996,2015,2147,1954,1832,1925,1988,1786,1960,1795,1801,1807,2004,1946,2098,1831,1979,1780,2079,2065,1972,2081,2057,2061,1856,2133,1867,1967,1991,1824,1819,1940,1802,2115,2020,1792,2102,2055,2014,2153,2071,2046,2024,2103,1939,2093,1814,1797,1915,1889,2125,2151,2110,2069,1816,2074,2033,2047,1859,1835,1864,1782,2002,1815,2138,2063,1857,2012,1818,1958,1813,1784,1980,1906,1773,2013,1827,2050,1934,1834,2034,1790,2141,2143,1823,1926,1905,2027,2152,2045,1788,1966,1785,1943,1976,1796,1945,2126,2072,1888,2122,1833,2003,2148,1758,1768,1769,1753,1764,1762,1812,1982,2123,1898,1760,2084,1896,2076,1872,2080,2078,1842,1767,1757,1897,1752,2106,1938,1771,2042,1941,1933,1985,2149,2091,1983,1875,1840,1974,1975,2038,1874,2155,1761,1963,2031,1916,1841,2118,2018,2137,2051,2085,2109,1759,2053,1820,2016,1880,1766,1755,1873,1775,2124,1843,2083,1883,1828,1973,1830,2007,1763,2017,1998,2059,1794,2121,1765,1993,1754,1977,1913,2026,2113,1917,1749,1770,2060,2043,1986,1935,2114,1850,1921,2100,2129,1899,1803,1811,1984,1965,2058,1942,1772,1776,2035,1808,2140,1804,1881,2086,2142,2021,1779,2039,2070,1929,1865,1798,2108,1957,1969,1995,1800,2117,2025,1809,1970,1952,1947,1931,2000,1806,1999,1821,1774,2066,1825,2101,2056,1948,2145,1930,1989,1949,1981,1791,2090,1950,2154,1944,2032,2048,2120,1987,1789,2136,2028,2089,2105,1787,2116,1956,1851,1829,1822,2054,1990,2041,1781,2096,1890,2073,2107,2094,2139,1799,2064,2052,2130,2132,2075,1907,1932,2023,2099,1927,2088,2068,2009,1918,2067,1961,2005,2006,2128,2119,2029,2134,2095,2092,2097,2040,1826,1866,1810,2082,1912,1891,2150,2135,2127,2049,1919,2146,2062,1778,1978,1959,1882,2111,2008,1955,1997,1904,1922,1848,2036,1964,1953,1962,1924,1994,1923,1968,2112,2001,2011,2144,1793,2010,1914,1858,1783,1971,2087,1992,1849,1805,2104,2131,1817,2037,1777,2030,2022,2077,1951,1928,1920,2044,2019,1996,2015,2147,1954,1832,1925,1988,1786,1960,1795,1801,1807,2004,1946,2098,1831,1979,1780,2079,2065,1972,2081,2057,2061,1856,2133,1867,1967,1991,1824,1819,1940,1802,2115,2020,1792,2102,2055,2014,2153,2071,2046,2024,2103,1939,2093,1814,1797,1915,1889,2125,2151,2110,2069,1816,2074,2033,2047,1859,1835,1864,1782,2002,1815,2138,2063,1857,2012,1818,1958,1813,1784,1980,1906,1773,2013,1827,2050,1934,1834,2034,1790,2141,2143,1823,1926,1905,2027,2152,2045,1788,1966,1785,1943,1976,1796,1945,2126,2072,1888,2122,1833,2003,2148,1758,1768,1769,1753,1764,1762,1812,1982,2123,1898,1760,2084,1896,2076,1872,2080,2078,1842,1767,1757,1897,1752,2106,1938,1771,2042,1941,1933,1985,2149,2091,1983,1875,1840,1974,1975,2038,1874,2155,1761,1963,2031,1916,1841,2118,2018,2137,2051,2085,2109,1759,2053,1820,2016,1880,1766,1755,1873,1775,2124,1843,2083,1883,1828,1973,1830,2007,1763,2017,1998,2059,1794,2121,1765,1993,1754,1977,1913,2026,2113,1917,1749,1770,2060,2043,1986,1935,2114,1850,1921,2100,2129,1899','[^,]+', 1, level)
from dual
connect by regexp_substr('1803,1811,1984,1965,2058,1942,1772,1776,2035,1808,2140,1804,1881,2086,2142,2021,1779,2039,2070,1929,1865,1798,2108,1957,1969,1995,1800,2117,2025,1809,1970,1952,1947,1931,2000,1806,1999,1821,1774,2066,1825,2101,2056,1948,2145,1930,1989,1949,1803,1811,1984,1965,2058,1942,1772,1776,2035,1808,2140,1804,1881,2086,2142,2021,1779,2039,2070,1929,1865,1798,2108,1957,1969,1995,1800,2117,2025,1809,1970,1952,1947,1931,2000,1806,1999,1821,1774,2066,1825,2101,2056,1948,2145,1930,1989,1949,1981,1791,2090,1950,2154,1944,2032,2048,2120,1987,1789,2136,2028,2089,2105,1787,2116,1956,1851,1829,1822,2054,1990,2041,1781,2096,1890,2073,2107,2094,2139,1799,2064,2052,2130,2132,2075,1907,1932,2023,2099,1927,2088,2068,2009,1918,2067,1961,2005,2006,2128,2119,2029,2134,2095,2092,2097,2040,1826,1866,1810,2082,1912,1891,2150,2135,2127,2049,1919,2146,2062,1778,1978,1959,1882,2111,2008,1955,1997,1904,1922,1848,2036,1964,1953,1962,1924,1994,1923,1968,2112,2001,2011,2144,1793,2010,1914,1858,1783,1971,2087,1992,1849,1805,2104,2131,1817,2037,1777,2030,2022,2077,1951,1928,1920,2044,2019,1996,2015,2147,1954,1832,1925,1988,1786,1960,1795,1801,1807,2004,1946,2098,1831,1979,1780,2079,2065,1972,2081,2057,2061,1856,2133,1867,1967,1991,1824,1819,1940,1802,2115,2020,1792,2102,2055,2014,2153,2071,2046,2024,2103,1939,2093,1814,1797,1915,1889,2125,2151,2110,2069,1816,2074,2033,2047,1859,1835,1864,1782,2002,1815,2138,2063,1857,2012,1818,1958,1813,1784,1980,1906,1773,2013,1827,2050,1934,1834,2034,1790,2141,2143,1823,1926,1905,2027,2152,2045,1788,1966,1785,1943,1976,1796,1945,2126,2072,1888,2122,1833,2003,2148,1758,1768,1769,1753,1764,1762,1812,1982,2123,1898,1760,2084,1896,2076,1872,2080,2078,1842,1767,1757,1897,1752,2106,1938,1771,2042,1941,1933,1985,2149,2091,1983,1875,1840,1974,1975,2038,1874,2155,1761,1963,2031,1916,1841,2118,2018,2137,2051,2085,2109,1759,2053,1820,2016,1880,1766,1755,1873,1775,2124,1843,2083,1883,1828,1973,1830,2007,1763,2017,1998,2059,1794,2121,1765,1993,1754,1977,1913,2026,2113,1917,1749,1770,2060,2043,1986,1935,2114,1850,1921,2100,2129,1899,1981,1791,2090,1950,2154,1944,2032,2048,2120,1987,1789,2136,2028,2089,2105,1787,2116,1956,1851,1829,1822,2054,1990,2041,1781,2096,1890,2073,2107,2094,2139,1799,2064,2052,2130,2132,2075,1907,1932,2023,2099,1927,2088,2068,2009,1918,2067,1961,2005,2006,2128,2119,2029,2134,2095,2092,2097,2040,1826,1866,1810,2082,1912,1891,2150,2135,2127,2049,1919,2146,2062,1778,1978,1959,1882,2111,2008,1955,1997,1904,1922,1848,2036,1964,1953,1962,1924,1994,1923,1968,2112,2001,2011,2144,1793,2010,1914,1858,1783,1971,2087,1992,1849,1805,2104,2131,1817,2037,1777,2030,2022,2077,1951,1928,1920,2044,2019,1996,2015,2147,1954,1832,1925,1988,1786,1960,1795,1801,1807,2004,1946,2098,1831,1979,1780,2079,2065,1972,2081,2057,2061,1856,2133,1867,1967,1991,1824,1819,1940,1802,2115,2020,1792,2102,2055,2014,2153,2071,2046,2024,2103,1939,2093,1814,1797,1915,1889,2125,2151,2110,2069,1816,2074,2033,2047,1859,1835,1864,1782,2002,1815,2138,2063,1857,2012,1818,1958,1813,1784,1980,1906,1773,2013,1827,2050,1934,1834,2034,1790,2141,2143,1823,1926,1905,2027,2152,2045,1788,1966,1785,1943,1976,1796,1945,2126,2072,1888,2122,1833,2003,2148,1758,1768,1769,1753,1764,1762,1812,1982,2123,1898,1760,2084,1896,2076,1872,2080,2078,1842,1767,1757,1897,1752,2106,1938,1771,2042,1941,1933,1985,2149,2091,1983,1875,1840,1974,1975,2038,1874,2155,1761,1963,2031,1916,1841,2118,2018,2137,2051,2085,2109,1759,2053,1820,2016,1880,1766,1755,1873,1775,2124,1843,2083,1883,1828,1973,1830,2007,1763,2017,1998,2059,1794,2121,1765,1993,1754,1977,1913,2026,2113,1917,1749,1770,2060,2043,1986,1935,2114,1850,1921,2100,2129,1899', '[^,]+', 1, level) is not null
order by 1;
is running about 23 seconds!! This is not acceptable at all.
What is the best approach for this "conversion"?
Best regards,
Jacek